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