public override void Read()
{
var progressinit = new ProgressInitEngine(OnProgressInit_Event);
progressinit(100, "Création/Remplissage de la table de comparaison");
CreateTheDataTable(out m_MergedTable);
InsertCsvRows(ref m_MergedTable, ref m_DeliveryXlsFileReader,
this.m_iDeliveryDateFormat, "D");
GroupUpDataTable();
var progressreport = new ProgressReportEngine(OnProgressReport_Event);
progressreport(25);
this.FillDataTable(ref m_MergedTable);
var completeproc = new CompleteEngineProcess(OnComplete_Event);
completeproc();
}
public override void Write()
{
var conn = new jbConnection(this.M_DBNAME, this.M_DBSERVER);
string sCurrentPO;
string sCurrentPart;
string sQuery;
string sSO;
int iCurrentLine = 0;
while (iCurrentLine < this.m_MergedTable.Rows.Count)
{
sCurrentPart = this.m_MergedTable.Rows[iCurrentLine][0].ToString();
sSO = this.m_MergedTable.Rows[iCurrentLine][1].ToString();
sCurrentPO = this.m_MergedTable.Rows[iCurrentLine][2].ToString();
int iPOLines = 1;
while ((iCurrentLine + iPOLines) < this.m_MergedTable.Rows.Count)
{
if ((this.m_MergedTable.Rows[iCurrentLine + iPOLines][0].ToString() == sCurrentPart) && (this.m_MergedTable.Rows[iCurrentLine + iPOLines][2].ToString() == sCurrentPO))
{
iPOLines++;
}
else
{
break;
}
}
if (sSO != "")
{
sQuery = "SELECT SO_Detail.Sales_Order, SO_Detail.SO_Line, SO_Detail.Order_Qty, "
+ "SO_Detail.Promised_Date, Address.Ship_To_ID, SO_Detail.Unit_Price, SO_Detail.SO_Detail, SO_Detail.Status "
+ "FROM SO_Detail LEFT JOIN Address ON SO_Detail.Ship_To = Address.Address "
+ "WHERE SO_Detail.Sales_Order = '" + sSO + "' AND (SO_Detail.Material LIKE '" + sCurrentPart + "') AND SO_Detail.Status NOT IN ('Shipped','Closed')"
+ "ORDER BY SO_Detail.SO_Detail";
var jbTable = conn.GetData(sQuery);
for (int i = 0; i < iPOLines; i++)
{
if (i < jbTable.Rows.Count)
{
string sUpdate = "UPDATE SO_Detail SET ";
string sUpdateDelivery;
bool bDeliveryUpdate = false;
sQuery = "SELECT SO_Detail FROM Delivery WHERE SO_Detail = " + jbTable.Rows[i][6].ToString();
var existingDeliveryTable = conn.GetData(sQuery);
if (existingDeliveryTable.Rows.Count > 0)
{
bDeliveryUpdate = true;
}
sUpdateDelivery = "UPDATE Delivery SET ";
string sDate = this.m_MergedTable.Rows[iCurrentLine + i][9].ToString();
string sQty = this.m_MergedTable.Rows[iCurrentLine + i][4].ToString();
string sLine = this.m_MergedTable.Rows[iCurrentLine + i][14].ToString();
string sDeliveryComment = this.m_MergedTable.Rows[iCurrentLine + i][23].ToString();
sUpdate += "Promised_Date = '" + sDate + "'";
sUpdateDelivery += "Promised_Date = '" + sDate + "' , Requested_Date = '" + sDate + "' ";
sUpdate += ", Order_Qty = " + sQty + ", Deferred_Qty = " + sQty;
sUpdateDelivery += ", Promised_Quantity = " + sQty + ", Remaining_Quantity = " + sQty + ", Comment = '" + sDeliveryComment + "' ";
string sSelect = "SELECT Unit_Price FROM SO_Detail WHERE SO_Detail.SO_Detail = " + jbTable.Rows[i][6].ToString();
var dTUnitPrice = conn.GetData(sSelect);
object oPrice = dTUnitPrice.Rows[0][0];
Double dUnitPrice = System.Convert.ToDouble(oPrice);
Double iQty = System.Convert.ToDouble(sQty);
string sNewPrice = (iQty * dUnitPrice).ToString();
sUpdate += ", Total_Price = " + sNewPrice;
sUpdate += ", SO_Line = " + sLine;
sSelect = "SELECT Sales_Code FROM Material WHERE Material LIKE '" + sCurrentPart + "'";
var dTSalesCode = conn.GetData(sSelect);
object oSalesCode = dTSalesCode.Rows[0][0];
if (oSalesCode != System.DBNull.Value)
{
sUpdate += ", Sales_Code = '" + oSalesCode.ToString() + "' ";
}
sUpdate += " WHERE SO_Detail = " + jbTable.Rows[i][6].ToString();
sUpdateDelivery += "WHERE SO_Detail = " + jbTable.Rows[i][6].ToString();
conn.SetData(sUpdate);
if (!bDeliveryUpdate)
{
sUpdateDelivery = "INSERT INTO Delivery (SO_Detail, Requested_Date, Promised_Date, Promised_Quantity, Remaining_Quantity, ObjectID) VALUES";
sUpdateDelivery += " (" + jbTable.Rows[i][6].ToString() + ", '" + sDate + "' , '" + sDate + "' ," + sQty + ", " + sQty +
", '" + System.Guid.NewGuid() + "')";
}
conn.SetData(sUpdateDelivery);
}
else
{
//Add an SO_Detail line to the SO
this.AddSODetailLine(this.m_MergedTable.Rows[iCurrentLine + i], sSO, this.m_MergedTable.Rows[iCurrentLine + i][14].ToString());
}
}
while (jbTable.Rows.Count > iPOLines)
{
conn.SetData("DELETE FROM Delivery WHERE Delivery.SO_Detail = " + jbTable.Rows[jbTable.Rows.Count - 1][6].ToString());
conn.SetData("DELETE FROM SO_Detail WHERE SO_Detail.SO_Detail = " + jbTable.Rows[jbTable.Rows.Count - 1][6].ToString());
jbTable.Rows.Remove(jbTable.Rows[jbTable.Rows.Count - 1]);
}
iCurrentLine += iPOLines;
}
else
{
sQuery = "SELECT Distinct(SO_Detail.Sales_Order) "
+ "FROM SO_Detail LEFT JOIN SO_Header ON SO_Detail.Sales_Order = SO_Header.Sales_Order "
+ "WHERE Material LIKE '" + sCurrentPart + "' AND SO_Header.Status LIKE 'Closed' AND "
+ "SO_Header.Customer LIKE '" + this.M_CUSTOMER_ID + "' AND SO_Header.Customer_PO = '" + sCurrentPO + "' ";
var jbClosedSOs = conn.GetData(sQuery);
if (jbClosedSOs.Rows.Count == 0)
{
//create SO
//and insert all the lines
string newSO = this.CreateNewSalesOrder(this.m_MergedTable.Rows[iCurrentLine]);
for (int i = 1; i < iPOLines; i++)
{
this.AddSODetailLine(this.m_MergedTable.Rows[iCurrentLine + i], newSO, this.m_MergedTable.Rows[iCurrentLine + i][14].ToString());
}
}
iCurrentLine += iPOLines;
}
}
conn.Dispose();
RemoveEmptyPackListDetails();
var completeproc = new CompleteEngineProcess(OnComplete_Event);
completeproc();
}
