public EdiDbInterface()
{
applicationName = "EDI";
var assembly = System.Reflection.Assembly.GetExecutingAssembly();
Version version = assembly.GetName().Version;
applicationVersion = version.ToString();
userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
machineName = System.Environment.MachineName;
var ediDbName = "MFG_EDI";
var conn = new jbConnection();
var dbServerLocation = conn.GetJbSettingsServer();
connectionStringToMfgDb = "Data Source=" + dbServerLocation
+ ";Initial Catalog=" + ediDbName
+ ";User ID=jobboss;Password=Bali;User Instance=false;"
+ "MultipleActiveResultSets=true";
try
{
var connectionToMfgDb = new System.Data.SqlClient.SqlConnection(
connectionStringToMfgDb);
connectionToMfgDb.Open();
connectionToMfgDb.Close();
}
catch (Exception e)
{
throw new DatabaseNotFoundException(e.Message);
}
}
static int Main(string[] args)
{
var mfgDbName = "MFG_EDI";
var conn = new jbConnection();
var dbServerLocation = conn.GetJbSettingsServer();
var connectionStringToMfgDb = "Data Source=" + dbServerLocation
+ ";Initial Catalog=" + mfgDbName
+ ";User ID=jobboss;Password=Bali;User Instance=false;Trusted_connection=true;MultipleActiveResultSets=false";
var upgrader =
DeployChanges.To
.SqlDatabase(connectionStringToMfgDb)
.WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
.LogToConsole()
.Build();
var result = upgrader.PerformUpgrade();
if (!result.Successful)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(result.Error);
Console.ResetColor();
#if DEBUG
Console.ReadLine();
#endif
return -1;
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Success!");
Console.ResetColor();
return 0;
}
protected string CreateNewSalesOrder(DataRow dr)
{
var conn = new jbConnection(this.M_DBNAME, this.M_DBSERVER);
int iSONumber;
var soNumber = conn.GetData(
"SELECT Last_Nbr FROM Auto_Number WHERE Type LIKE 'SalesOrder'");
iSONumber = Convert.ToInt32(soNumber.Rows[0][0].ToString());
iSONumber++;
soNumber.Dispose();
conn.SetData("UPDATE Auto_Number SET Last_Nbr = " + EscapeSQLString((iSONumber).ToString()) + " WHERE Type LIKE 'SalesOrder'");
DataTable dtCustomerCurrency = conn.GetData("SELECT Currency_Def FROM Customer WHERE Customer LIKE '" + EscapeSQLString(this.M_CUSTOMER_ID) + "'");
string sCustomerCurrency = dtCustomerCurrency.Rows[0][0].ToString();
if (sCustomerCurrency == "")
{
dtCustomerCurrency = conn.GetData("SELECT System_Base_Currency FROM Preferences WHERE Preferences = 1");
sCustomerCurrency = dtCustomerCurrency.Rows[0][0].ToString();
}
DateTime dtToday = DateTime.Now;
string sToday = dtToday.Month + "/" + dtToday.Day + "/" + dtToday.Year;
string PO = dr[2].ToString();
string sStatus = this.M_SO_CREATION_STATUS;
//create the sales order
string sQuery;
sQuery = "INSERT INTO SO_Header (Sales_Order, Customer, Customer_PO, Sales_Tax_Amt, Sales_Tax_Rate, Order_Date, Promised_Date, Status, "
+ "Total_Price, Trade_Currency, Currency_Conv_Rate) VALUES ('" + EscapeSQLString(iSONumber.ToString()) + "', '" + EscapeSQLString(this.M_CUSTOMER_ID) + "', '" + EscapeSQLString(PO) + "', 0.00, 0, '" + EscapeSQLString(sToday) + "', '" + EscapeSQLString(sToday) + "', '" + EscapeSQLString(sStatus) + "', 0, " + EscapeSQLString(sCustomerCurrency) + ", 1)";
conn.SetData(sQuery);
try
{
string sLineNumber = dr[14].ToString();
if (dr[18].ToString() == "F")
{
sLineNumber = "FORE";
}
this.AddSODetailLine(dr, iSONumber.ToString(), sLineNumber);
}
catch (MissingAddressFieldException e)
{
throw e;
}
return(System.Convert.ToString(iSONumber));
}
protected int AcquireNextSalesOrderNumberAndIncrementAutoNumber()
{
var conn = new jbConnection(this.M_DBNAME, this.M_DBSERVER);
var query = "SELECT Last_Nbr FROM Auto_Number WHERE Type LIKE 'SalesOrder'";
DataTable dt = conn.GetData(query);
int soNumber = Convert.ToInt32(dt.Rows[0][0]);
soNumber++;
var updateAutoNumberQuery = "UPDATE Auto_Number SET "
+ "Last_Nbr = " + EscapeSQLString(soNumber.ToString()) + " "
+ "WHERE Type LIKE 'SalesOrder'";
conn.SetData(updateAutoNumberQuery);
return(soNumber);
}
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();
}
public void CreateTheDataTable(out DataTable m_DataTable)
{
var conn = new jbConnection(this.M_DBNAME, this.M_DBSERVER);
System.Data.DataTable so_header_schema = conn.getSchema("SO_Header");
System.Data.DataTable so_detail_schema = conn.getSchema("SO_Detail");
//Create an empty table of the format required for this application
m_DataTable = new DataTable();
string sColName;
string sColType;
string sColLength;
//0 Material
var theRow = so_detail_schema.Select("COLUMN_NAME = 'Material'");
sColName = theRow[0]["COLUMN_NAME"].ToString(); //so_detail_structure[31][0];
sColType = this.ProperTypeString(theRow[0]["DATA_TYPE"].ToString()); //so_detail_structure[31][1];
sColLength = theRow[0]["CHARACTER_MAXIMUM_LENGTH"].ToString(); //so_detail_structure[31][2];
AddColumnToTable(ref m_DataTable, sColName, "Material", sColType, Convert.ToInt32(sColLength), true);
//1 Sales_Order
theRow = so_detail_schema.Select("COLUMN_NAME = 'Sales_Order'");
sColName = theRow[0]["COLUMN_NAME"].ToString(); //so_detail_structure[1][0];
sColType = this.ProperTypeString(theRow[0]["DATA_TYPE"].ToString()); //so_detail_structure[1][1];
sColLength = theRow[0]["CHARACTER_MAXIMUM_LENGTH"].ToString(); //so_detail_structure[1][2];
AddColumnToTable(ref m_DataTable, sColName, "Sales Order", this.ProperTypeString(sColType), Convert.ToInt32(sColLength), true);
//2 PO
theRow = so_header_schema.Select("COLUMN_NAME = 'Customer_PO'");
sColName = theRow[0]["COLUMN_NAME"].ToString(); //so_header_structure[13][0];
sColType = this.ProperTypeString(theRow[0]["DATA_TYPE"].ToString()); //so_header_structure[13][1]; // <- "text"
sColLength = theRow[0]["CHARACTER_MAXIMUM_LENGTH"].ToString(); //so_header_structure[13][2];
AddColumnToTable(ref m_DataTable, sColName, "PO", sColType, Convert.ToInt32(sColLength), true);
//3 Qty_Old
theRow = so_detail_schema.Select("COLUMN_NAME = 'Order_Qty'");
sColName = theRow[0]["COLUMN_NAME"].ToString(); //so_detail_structure[17][0];
sColType = this.ProperTypeString(theRow[0]["DATA_TYPE"].ToString()); //so_detail_structure[17][1];
sColLength = theRow[0]["CHARACTER_MAXIMUM_LENGTH"].ToString(); //so_detail_structure[17][2];
AddColumnToTable(ref m_DataTable, sColName, "Qty Old", sColType,
Convert.ToInt32(this.GiveCorrectLength(sColType, sColLength)), true);
//4 Qty_New ---.Comes from Excel file and not jbDB
sColName = "NewQty";
AddColumnToTable(ref m_DataTable, sColName, "Qty New", sColType,
Convert.ToInt32(this.GiveCorrectLength(sColType, sColLength)), true);
//5 Qty_Diff
sColName = "Qty_Diff";
AddColumnToTable(ref m_DataTable, sColName, "Qty Diff", sColType,
Convert.ToInt32(this.GiveCorrectLength(sColType, sColLength)), true);
//6 RefuseNewQty
sColName = "RefuseNewQty";
sColType = "varchar";
sColLength = "50";
AddColumnToTable(ref m_DataTable, sColName, "Refuse New Qty",
this.ProperTypeString(sColType), Convert.ToInt32(sColLength), true);
//7 Qty_Diff_Value
sColName = "Qty_Diff_Value";
sColType = "double";
AddColumnToTable(ref m_DataTable, sColName, "Qty Diff Value",
this.ProperTypeString(sColType), Convert.ToInt32(sColLength), true);
//8 Date_Old
theRow = so_detail_schema.Select("COLUMN_NAME = 'Promised_Date'");
sColName = theRow[0]["COLUMN_NAME"].ToString(); //so_detail_structure[30][0];
sColType = this.ProperTypeString(theRow[0]["DATA_TYPE"].ToString()); //so_detail_structure[30][1];
sColLength = theRow[0]["CHARACTER_MAXIMUM_LENGTH"].ToString(); //so_detail_structure[30][2];
AddColumnToTable(ref m_DataTable, sColName, "Date Old", sColType,
Convert.ToInt32(this.GiveCorrectLength(sColType, sColLength)), true);
//9 Date_New
sColName = "Date_New";
AddColumnToTable(ref m_DataTable, sColName, "Date New", sColType,
Convert.ToInt32(this.GiveCorrectLength(sColType, sColLength)), true);
//10 Date_Diff
sColName = "Date_Diff";
sColType = this.ProperTypeString("int");
AddColumnToTable(ref m_DataTable, sColName, "Date Diff", sColType,
Convert.ToInt32(this.GiveCorrectLength(sColType, sColLength)), true);
//11 RefuseNewDate
sColName = "RefuseNewDate";
sColType = "text";
sColLength = "50";
AddColumnToTable(ref m_DataTable, sColName, "Refuse New Date",
this.ProperTypeString(sColType), Convert.ToInt32(sColLength), true);
//12 Date_Diff_Value
sColName = "Date_Diff_Value";
sColType = "double";
AddColumnToTable(ref m_DataTable, sColName, "Date Diff Value",
this.ProperTypeString(sColType), Convert.ToInt32(sColLength), true);
//13 DeliveryNewLocation
sColName = "DeliveryNewLocation";
sColType = "text";
sColLength = "6";
AddColumnToTable(ref m_DataTable, sColName, "Plant de livraison", this.ProperTypeString(sColType), Convert.ToInt32(sColLength), true);
//14 Excel File Detail Line
sColName = "NewLineNumber";
sColType = "text";
sColLength = "6";
AddColumnToTable(ref m_DataTable, sColName, "# de ligne du fichier",
ProperTypeString(sColType), Convert.ToInt32(sColLength), true);
//15 SO Detail Line
sColName = "JBLineNumber";
sColType = "text";
sColLength = "6";
theRow = so_detail_schema.Select("COLUMN_NAME = 'SO_Line'");
sColName = theRow[0]["COLUMN_NAME"].ToString();
sColType = this.ProperTypeString(theRow[0]["DATA_TYPE"].ToString());
sColLength = theRow[0]["CHARACTER_MAXIMUM_LENGTH"].ToString();
AddColumnToTable(ref m_DataTable, sColName, "# de ligne du SO_Detail", this.ProperTypeString(sColType), Convert.ToInt32(sColLength), true);
//16 SO Detail
sColName = "JBSODetail";
sColType = "int";
sColLength = "6";
theRow = so_detail_schema.Select("COLUMN_NAME = 'SO_Detail'");
sColName = theRow[0]["COLUMN_NAME"].ToString();
sColType = this.ProperTypeString(theRow[0]["DATA_TYPE"].ToString());
sColLength = theRow[0]["CHARACTER_MAXIMUM_LENGTH"].ToString();
AddColumnToTable(ref m_DataTable, sColName, "SO_Detail", this.ProperTypeString(sColType),
Convert.ToInt32(this.GiveCorrectLength(sColType, sColLength)), true);
//17 SO Detail Status
sColName = "SO_Detail_Status";
sColType = "text";
sColLength = "10";
theRow = so_detail_schema.Select("COLUMN_NAME = 'Status'");
sColName = theRow[0]["COLUMN_NAME"].ToString();
sColType = this.ProperTypeString(theRow[0]["DATA_TYPE"].ToString());
sColLength = theRow[0]["CHARACTER_MAXIMUM_LENGTH"].ToString();
AddColumnToTable(ref m_DataTable, sColName, "SO Detail Status", this.ProperTypeString(sColType), Convert.ToInt32(sColLength), true);
//18 Unit Price
sColName = "UnitPrice";
sColType = "double";
sColLength = "6";
theRow = so_detail_schema.Select("COLUMN_NAME = 'Unit_Price'");
sColName = theRow[0]["COLUMN_NAME"].ToString();
sColType = this.ProperTypeString(theRow[0]["DATA_TYPE"].ToString());
sColLength = theRow[0]["CHARACTER_MAXIMUM_LENGTH"].ToString();
AddColumnToTable(ref m_DataTable, sColName, "Prix Unitaire", this.ProperTypeString(sColType),
Convert.ToInt32(this.GiveCorrectLength(sColType, sColLength)), true);
//19 FileOrigin
sColName = "FileOrigin";
sColType = "text";
sColLength = "4";
AddColumnToTable(ref m_DataTable, sColName, "Delivery || Forecast", this.ProperTypeString(sColType), Convert.ToInt32(sColLength), true);
//20 QtyCumulated
sColName = "Qty Cum.";
sColType = "int";
AddColumnToTable(ref m_DataTable, sColName, "Qté cum reçue",
this.ProperTypeString(sColType), Convert.ToInt32(sColLength), true);
//21 Source
sColName = "Source";
sColType = "text";
sColLength = "6";
AddColumnToTable(ref m_DataTable, sColName, "Source", this.ProperTypeString(sColType), Convert.ToInt32(sColLength), true);
//22 Source
sColName = "Client";
sColType = "text";
sColLength = "10";
AddColumnToTable(ref m_DataTable, sColName, "Client", this.ProperTypeString(sColType), Convert.ToInt32(sColLength), true);
//23 Source
sColName = "DeliveryComment";
sColType = "text";
sColLength = "2147483647";
AddColumnToTable(ref m_DataTable, sColName, "Commentaire de livraison", this.ProperTypeString(sColType), Convert.ToInt32(sColLength), true);
}
public void FillDataTable(ref DataTable m_DataTable)
{
var conn = new jbConnection(this.M_DBNAME, this.M_DBSERVER);
this.PreemptiveVerification(ref this.m_MergedTable);
this.RemoveMaterialsToBeIgnored(ref this.m_MergedTable,
ref this.m_MaterialsToBeIgnored);
int iCurrentLine = 0;
OnProgressInitOutside(m_DataTable.Rows.Count, "Remplissage avec les données de JobBOSS"); // TODO: When converting to C# change OnProgressInitOutside to OnProgressInit and get rid of the method in EDI_Engine_Base
string sCurrentPO;
string sCurrentPart;
string sQuery;
while (iCurrentLine < m_DataTable.Rows.Count)
{
sCurrentPart = m_DataTable.Rows[iCurrentLine][0].ToString();
sCurrentPO = m_DataTable.Rows[iCurrentLine][2].ToString();
int iPOLines = 1;
while ((iCurrentLine + iPOLines) < m_DataTable.Rows.Count)
{
if ((m_DataTable.Rows[iCurrentLine + iPOLines][0].ToString() == sCurrentPart) && (m_DataTable.Rows[iCurrentLine + iPOLines][2].ToString() == sCurrentPO))
{
iPOLines++;
}
else
{
break;
}
}
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 IN ('Open', 'Hold') AND "
+ "SO_Header.Customer LIKE '" + this.M_CUSTOMER_ID + "' AND SO_Header.Customer_PO = '" + sCurrentPO + "' ";
var jbSO = conn.GetData(sQuery);
if (jbSO != null)
{
if (jbSO.Rows.Count > 1)
{
MessageBox.Show("Vous avez plus d'un Sales Order pour la pièce " + sCurrentPart + " pour votre "
+ "client EDI. Veuillez vous assurer que nous n'en avons juste un svp.");
System.Threading.Thread.CurrentThread.Abort();
}//end if
else if (jbSO.Rows.Count == 1)
{
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 = '" + jbSO.Rows[0][0] + "' 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);
if (jbTable.Rows.Count > 0)
{
int iLinesToMatch;
if (jbTable.Rows.Count > iPOLines)
{
iLinesToMatch = iPOLines;
}
else
{
iLinesToMatch = jbTable.Rows.Count;
}
for (int x = 0; x < iLinesToMatch; x++)
{
m_DataTable.Rows[iCurrentLine][1] = jbTable.Rows[x][0].ToString();
m_DataTable.Rows[iCurrentLine][3] = jbTable.Rows[x][2].ToString();
m_DataTable.Rows[iCurrentLine][8] = jbTable.Rows[x][3].ToString();
this.PerformCalculationsForRow(ref m_DataTable, iCurrentLine);
iCurrentLine++;
}
if (iPOLines > jbTable.Rows.Count)
{
int iRemainingPOLines = iPOLines - jbTable.Rows.Count;
for (int x = 0; x < iRemainingPOLines; x++)
{
m_DataTable.Rows[iCurrentLine][1] = jbTable.Rows[0][0].ToString();
m_DataTable.Rows[iCurrentLine][3] = m_DataTable.Rows[iCurrentLine][4];
m_DataTable.Rows[iCurrentLine][8] = m_DataTable.Rows[iCurrentLine][9];
this.PerformCalculationsForRow(ref m_DataTable, iCurrentLine);
iCurrentLine++;
}
}
}
else
{ //All the lines of this Sales Order are closed or shipped. We must therefore add all of the file lines to new Sales Order detail lines.
for (int x = 0; x < iPOLines; x++)
{
m_DataTable.Rows[iCurrentLine][1] = jbSO.Rows[0][0].ToString();
m_DataTable.Rows[iCurrentLine][3] = 0;
m_DataTable.Rows[iCurrentLine][8] = m_DataTable.Rows[iCurrentLine][9];
this.PerformCalculationsForRow(ref m_DataTable, iCurrentLine);
iCurrentLine++;
}
}
iCurrentLine--;
}
}
else
{
for (int i = 0; i < iPOLines; i++)
{
m_DataTable.Rows[iCurrentLine + i][3] = "0";
m_DataTable.Rows[iCurrentLine + i][8] = m_DataTable.Rows[iCurrentLine][9];
this.PerformCalculationsForRow(ref m_DataTable, iCurrentLine + i);
}
}
iCurrentLine++;
}
}
protected void AddSODetailLine(DataRow dr, string sSONumber, string sLine)
{
var conn = new jbConnection(this.M_DBNAME, this.M_DBSERVER);
string sDifferentialQty;
string sDifferentialDate;
string sMaterial = dr[0].ToString();
string sPlant = dr[13].ToString();
if (dr[22].ToString() != "")
{
this.M_CUSTOMER_ID = dr[22].ToString();
}
if (sPlant == "0")
{
MessageBox.Show("'Plant 0'...EDI essaie de crée une ligne alors qu'il devrait ne rien faire.");
}
if (dr[3] == DBNull.Value)
{
sDifferentialQty = dr[4].ToString();
}
else
{
sDifferentialQty = dr[3].ToString();
}
if (dr[10] == DBNull.Value)
{
sDifferentialDate = "0";
}
else
{
sDifferentialDate = dr[10].ToString();
}
var address = conn.GetData("SELECT Address FROM Address WHERE Customer LIKE '"
+ EscapeSQLString(this.M_CUSTOMER_ID) + "' AND Ship_To_ID LIKE '" + EscapeSQLString(dr[13].ToString()) + "'");
try
{
if (address.Rows.Count < 1)
{
throw new MissingAddressFieldException("Le plant de livraison " + dr[13] +
" n'existe pas dans votre base de données.(Base) - Mat: " + dr[0] + " | SO: " + dr[1]);
}
}
catch (MissingAddressFieldException e)
{
MessageBox.Show(e.Message);
throw e;
}
var addressID = (address.Rows[0][0]).ToString();
var dtInvCosting = conn.GetData("SELECT Inv_Cost_Method FROM Preferences");
var sInvCostingMethod = dtInvCosting.Rows[0][0].ToString();
string sQuery = "SELECT Material, Selling_Price, Price_UofM, Stocked_UofM, Cost_UofM, Sales_Code, Rev FROM Material WHERE Material LIKE '" + EscapeSQLString(sMaterial) + "'";
var dtMaterialInfo = conn.GetData(sQuery);
string sUnitPrice = dtMaterialInfo.Rows[0][1].ToString();
string sPriceUofM = dtMaterialInfo.Rows[0][2].ToString();
string sOrderQty = dr[4].ToString();
string sStockUofM = dtMaterialInfo.Rows[0][3].ToString();
string sCostUofM = dtMaterialInfo.Rows[0][4].ToString();
object oSalesCode = dtMaterialInfo.Rows[0][5];
object oRev;
if (this.M_CUSTOMER_ID == "PRINOTH")
{
oRev = dr[15].ToString();
}
else
{
oRev = dtMaterialInfo.Rows[0][6];
}
var arPromisedDate = this.FormatJBDate(dr[9].ToString());
string sPromisedDate = arPromisedDate[1] + "/"
+ arPromisedDate[0] + "/" + arPromisedDate[2];
string sTotalPrice = (Convert.ToDouble(sUnitPrice) * Convert.ToDouble(sOrderQty)).ToString();
string sStatus = this.M_SO_CREATION_STATUS;
//create the detail line
sQuery = "INSERT INTO SO_Detail (Sales_Order, SO_Line, Ship_To, Status, Make_Buy, Unit_Price, "
+ "Price_UofM, Total_Price, Order_Qty, Stock_UofM, Deferred_Qty, Promised_Date, Material, Cost_UofM ,"
+ "ObjectID, Unit_Cost, Shipped_Qty, Returned_Qty";
if (oSalesCode != DBNull.Value)
{
sQuery += ", Sales_Code";
}
if (oRev != DBNull.Value)
{
sQuery += ", Rev";
}
sQuery += ") VALUES ('" + EscapeSQLString(sSONumber) + "', '" + EscapeSQLString(sLine) + "', " + EscapeSQLString(addressID) + ", '" + EscapeSQLString(sStatus) + "', 'M', " + EscapeSQLString(sUnitPrice)
+ ", '" + EscapeSQLString(sPriceUofM) + "', " + EscapeSQLString(sTotalPrice) + ", " + EscapeSQLString(sOrderQty) + ", '" + EscapeSQLString(sStockUofM) + "'"
+ ", " + EscapeSQLString(sOrderQty) + ", '" + EscapeSQLString(sPromisedDate) + "', '" + EscapeSQLString(sMaterial) + "', '" + EscapeSQLString(sCostUofM) + "', '" + EscapeSQLString(Guid.NewGuid().ToString()) + "', 0, 0, 0";
if (oSalesCode != DBNull.Value)
{
sQuery += ", '" + EscapeSQLString(oSalesCode.ToString()) + "'";
}
if (oRev != DBNull.Value)
{
sQuery += ", '" + EscapeSQLString(oRev.ToString()) + "'";
}
sQuery += ")";
conn.SetData(sQuery);
var dtLatestSoDetail = conn.GetData("SELECT MAX(SO_Detail) FROM SO_Detail");
sQuery = "INSERT INTO Delivery (SO_Detail, Requested_Date, Promised_Date, Promised_Quantity, Remaining_Quantity, ObjectID) "
+ "VALUES (" + EscapeSQLString(dtLatestSoDetail.Rows[0][0].ToString()) + ", '" + EscapeSQLString(sPromisedDate) + "', '" + EscapeSQLString(sPromisedDate) + "', "
+ EscapeSQLString(sOrderQty) + " , " + EscapeSQLString(sOrderQty) + ", '" + EscapeSQLString(Guid.NewGuid().ToString()) + "')";
conn.SetData(sQuery);
this.UpdateSOTotalPrice(sSONumber);
this.AddToHistory(new string[] { sDifferentialQty, sDifferentialDate, sUnitPrice, sOrderQty });
}
