September 4, 2005 at 1:31 am
namespace CelesticaPower.UserControls{using System;using System.Collections;using System.Configuration;using System.Data;using System.Data.OleDb;using System.Data.SqlClient;using System.Drawing;using System.IO;using System.Text;using System.Web;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls;using System.Reflection; // For Missing.Value and BindingFlags//using System.Runtime.InteropServices; // For COMException//using Excel;/// <summary>///Summary description for ProcessBOMDemand./// </summary>public abstract class ProcessBOMRiskAnalysis : System.Web.UI.UserControl{protected System.Web.UI.WebControls.Label ResultLabel;protected System.Web.UI.WebControls.Label ErrorLabel;protected System.Web.UI.WebControls.HyperLink HyperLinkBOM;private void Page_Load(object sender, System.EventArgs e){if (!IsPostBack){DateTime effectiveDate;try{effectiveDate = System.DateTime.Parse(Request.QueryString["EffDate"]);}catch{ErrorLabel.Text = "You must specify a valid effectivity date. Press the Back button to enter an effectivity date.";ErrorLabel.Visible = true;return;}if (null == Request.QueryString["EndItem"] || 0 == Request.QueryString["EndItem"].Length){ErrorLabel.Text = "You must specify an end item. Press the Back button to enter an end item.";ErrorLabel.Visible = true;}else{string fsTableString = "ITEM_MULTILEVELBILL";ADODB.Recordset engMAdoRS = null;ADODB.Recordset apprVendAdoRS = null;//Application app = null;OleDbConnection objConnDemand = null;OleDbCommand objCmdDemand;OleDbConnection objConnCost = null;OleDbCommand objCmdCost;OleDbConnection objConnInv = null;OleDbCommand objCmdInv;try{//string dataDir = ConfigurationSettings.AppSettings["BOMDataDir"];//string newXlsFile = dataDir + Request.QueryString["EndItem"] + "BOMRisk.xls";// Copy the xls file.//File.Copy(dataDir + "BOMRisk.xls", newXlsFile, true);// Begin the xls string.StringBuilder outputStr = new StringBuilder("");outputStr.Append("<HTML>");outputStr.Append("<BODY>");outputStr.Append("<TABLE BORDER=1 CELLSPACING=1 CELLPADDING=1>");outputStr.Append("<TR>");outputStr.Append("<TD colspan='2'>C&D Technologies, Inc.</TD>");outputStr.Append("</TR>");outputStr.Append("<TR colspan='2'>");outputStr.Append("<TD>Confidential</TD>");outputStr.Append("</TR>");outputStr.Append("<TR>");outputStr.Append("<TD>End Item</TD>");outputStr.Append("<TD>" + Request.QueryString["EndItem"].Trim() + "</TD>");outputStr.Append("</TR>");outputStr.Append("<TR colspan='2'>");outputStr.Append("<TD>BOM Risk Analysis Report</TD>");outputStr.Append("</TR>");outputStr.Append("</TABLE>");outputStr.Append("<TABLE BORDER=1 CELLSPACING=1 CELLPADDING=1>");outputStr.Append("<TR>");outputStr.Append("<TD>END_ITEM</TD>");outputStr.Append("<TD>COMPONENT</TD>");outputStr.Append("<TD>COMP_REV</TD>");outputStr.Append("<TD>COMP_DESC</TD>");outputStr.Append("<TD>COM_TYP</TD>");outputStr.Append("<TD>SUM_QTY</TD>");outputStr.Append("<TD>COMP_UM</TD>");outputStr.Append("<TD>COMP_MB</TD>");outputStr.Append("<TD>C_FIX_LT</TD>");outputStr.Append("<TD>C_INSP_LT</TD>");outputStr.Append("<TD>BUYER_PLANNER</TD>");outputStr.Append("<TD>COMP_MATL</TD>");outputStr.Append("<TD>STD_COST</TD>");outputStr.Append("<TD>CNTRACT_PRICE</TD>");outputStr.Append("<TD>ASIA_COST</TD>");outputStr.Append("<TD>OHND_QTY</TD>");outputStr.Append("<TD>INSP_QTY</TD>");outputStr.Append("<TD>INHOUSE</TD>");outputStr.Append("<TD>ON_ORD_QTY</TD>");outputStr.Append("<TD>CUR_DMND1</TD>");outputStr.Append("<TD>EXCESSINV</TD>");outputStr.Append("<TD>SOURCE</TD>");outputStr.Append("<TD>AVL_NUMS</TD>");outputStr.Append("<TD>ITEM_REF1</TD>");outputStr.Append("<TD>PARENT_REV</TD>");outputStr.Append("<TD>LOT_SZ_MUL</TD>");outputStr.Append("</TR>");// Get the ML BOM data into a dataset.string myConnString = ConfigurationSettings.AppSettings["PorFSConnectionString"];SqlConnection myConnection = new SqlConnection(myConnString);SqlCommand myCommand = new SqlCommand("por_SelectMLBomRisk '" + Request.QueryString["EndItem"].Trim() + "'", myConnection);SqlDataAdapter bomDA = new SqlDataAdapter();bomDA.SelectCommand = myCommand;DataSet mlBomDS = new DataSet();myConnection.Open();bomDA.Fill(mlBomDS, fsTableString);myConnection.Close();// If there is data for the specified end item, // get the rest of the data.if (mlBomDS.Tables[fsTableString].Rows.Count > 0){// Get the list of items so that we can get the rev of each component.myConnection = new SqlConnection(myConnString);myCommand = new SqlCommand("SELECT ItemNumber AS ITEM, ItemRevision AS REV, LotSizeMultiplier AS LOT_SZ_MUL FROM FS_Item ORDER BY ItemNumber", myConnection);bomDA = new SqlDataAdapter();bomDA.SelectCommand = myCommand;myConnection.Open();bomDA.Fill(mlBomDS, "ITEM_INFO");myConnection.Close();System.Data.DataTable itemTable = mlBomDS.Tables["ITEM_INFO"];// Get the Engineering Module data.ADODB.Connection engMConn = new ADODB.Connection();engMConn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;engMConn.Mode = ADODB.ConnectModeEnum.adModeRead;engMConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=T:\\Data\\engm\\ENGM32-1.mdb;"+ " OLE DB Services = 0;", null, null, -1);engMAdoRS = new ADODB.Recordset();engMAdoRS.CursorLocation = ADODB.CursorLocationEnum.adUseClient;engMAdoRS.Open("SELECT RefDes, RD_Tag, Item, Parent, RD_IN_EFFECT, RD_OUT_EFFECT FROM RefDes", engMConn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 0);OleDbDataAdapter refDesDA = new OleDbDataAdapter();DataSet refDesDS = new DataSet();refDesDA.Fill(refDesDS, engMAdoRS, "RefDes");engMConn.Close();System.Data.DataTable refDesTable;refDesTable = refDesDS.Tables["RefDes"];string RefDesString = "";SortedList refDesignators;string avlString = "";SortedList avlNumbers;// Get the Cost table.objConnCost = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=T:\\Data\\Xtracts\\From4thS\\COSTX.MDB");objConnCost.Open();objCmdCost = new System.Data.OleDb.OleDbCommand();objCmdCost.Connection = objConnCost;objCmdCost.CommandText = "SELECT ITEM, COST_CODE, COST_TYPE, MATL, TOT_ROLCST "+ "FROM COST "+ "WHERE (COST.COST_CODE='1' "+ "OR COST.COST_CODE ='3' "+ "OR COST.COST_CODE ='5') "+ " OR (COST.COST_TYPE='0' AND COST.COST_CODE='4')";OleDbDataAdapter appCostDA = new OleDbDataAdapter();appCostDA.SelectCommand = objCmdCost;DataSet appCostDS = new DataSet();appCostDA.Fill(appCostDS, "Cost");System.Data.DataTable appCostTable;appCostTable = appCostDS.Tables["Cost"];objConnCost.Close();// Get the Inventory table.objConnInv = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=T:\\Data\\Xtracts\\From4thS\\INVX.MDB");objConnInv.Open();objCmdInv = new System.Data.OleDb.OleDbCommand();objCmdInv.Connection = objConnInv;objCmdInv.CommandText = "SELECT ITEM, OHND_QTY, INSP_QTY, ON_ORD_QTY, MATL "+ "FROM INV";OleDbDataAdapter appInvDA = new OleDbDataAdapter();appInvDA.SelectCommand = objCmdInv;DataSet appInvDS = new DataSet();appInvDA.Fill(appInvDS, "Inv");System.Data.DataTable appInvTable;appInvTable = appInvDS.Tables["Inv"];objConnInv.Close();// Get the Demand table.objConnDemand = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=T:\\Data\\Xtracts\\From4thS\\DEMANDX.MDB");objConnDemand.Open();objCmdDemand = new System.Data.OleDb.OleDbCommand();objCmdDemand.Connection = objConnDemand;objCmdDemand.CommandText = "SELECT ITEM, REQD_QTY, ISSUED_QTY, COMP_STA, COM_TYP " + " FROM Demand";OleDbDataAdapter appDemandDA = new OleDbDataAdapter();appDemandDA.SelectCommand = objCmdDemand;DataSet appDemandDS = new DataSet();appDemandDA.Fill(appDemandDS, "Demand");System.Data.DataTable appDemandTable;appDemandTable = appDemandDS.Tables["Demand"];objConnDemand.Close();// Get the Approved Vendors table.apprVendAdoRS = new ADODB.Recordset();apprVendAdoRS.CursorLocation = ADODB.CursorLocationEnum.adUseClient;apprVendAdoRS.Open("SELECT Item, VItem, Vendor_ID, Doc, Status, Date, Quot_Lo, Quot_Hi FROM AS060101", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=T:\\Data\\Xtracts\\From4thS\\" + ";Extended Properties=dBASE 5.0;", ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 0);OleDbDataAdapter appVendDA = new OleDbDataAdapter();DataSet appVendDS = new DataSet();appVendDA.Fill(appVendDS, apprVendAdoRS, "AS060101");System.Data.DataTable appVendTable;appVendTable = appVendDS.Tables["AS060101"];System.Data.DataTable componentsTable;componentsTable = mlBomDS.Tables["ITEM_MULTILEVELBILL"];// Add a field to keep track of whether this part of the BOM is in effectivity.componentsTable.Columns.Add("IsInEffectivity", typeof(Int32));// Add a field to keep track of the extended quantity of each component in the BOM.componentsTable.Columns.Add("ExtendedQuantity", typeof(Decimal));DataRow[] foundRows, foundDemands, foundInv, foundCosts, foundRefDes, foundVendors, foundItems;int levelOfComponent = 0;bool assemblyIsInEffectivity = true;int levelThatIsOutOfEffectivity = Int32.MaxValue;// Create a SortedList that will store the quantity at each level.SortedList quantitiesAtLevel = new SortedList();// Set the quantity of the first level (the end item) to 1.quantitiesAtLevel[0] = (Decimal)1;// Create a var to hold the calcuated extended quantity.Decimal extendedQty = (Decimal)0;// Figure out which records are in effectivity and which ones are not.// Also keep track of the quantities used at each level so that we can// give an accurate quantity for each component in the BOMforeach (DataRow pRow in mlBomDS.Tables[fsTableString].Rows){// Store the level that this component is at.levelOfComponent = Int32.Parse(pRow["LEVEL"].ToString());// Make sure that we are not inside an assembly that is out of effectivity.// IOW, make sure that an ancestor of this component is not out of date.// We do this by seeing if "assemblyIsInEffectivity" is true or if this // component's level is at or above the last component that was not in effectivity.if (true == assemblyIsInEffectivity || levelOfComponent <= levelThatIsOutOfEffectivity){// See if this particular component is out of effectivity.if (((DateTime)pRow["IN_EFFECT"]) > effectiveDate || ((DateTime)pRow["OUT_EFFECT"]) < effectiveDate ||((string)pRow["ITEM_TYPE"]).Equals("T") ||((string)pRow["COM_TYP"]).Equals("T")){// If it is out of effectivity, set the flags so that we don't use its// child components. assemblyIsInEffectivity = false;levelThatIsOutOfEffectivity = levelOfComponent;// Update the "IsInEffectivity" field on this record.pRow["IsInEffectivity"] = 0;}else{// Set the flag to indicate that we are in effectivity. assemblyIsInEffectivity = true;// Update the "IsInEffectivity" field on this record.pRow["IsInEffectivity"] = 1;// Cache the quantity for this level.quantitiesAtLevel[levelOfComponent] = Decimal.Parse((pRow["Quantity"]).ToString(), System.Globalization.NumberStyles.AllowThousands | System.Globalization.NumberStyles.AllowDecimalPoint | System.Globalization.NumberStyles.AllowLeadingWhite | System.Globalization.NumberStyles.AllowTrailingWhite | System.Globalization.NumberStyles.AllowLeadingSign);// Set the extended quantity at 1 to get started.extendedQty = (Decimal)1;// Figure out the extended quantity for this component.for ( int i = 0; i <= levelOfComponent; i++ ) {extendedQty = extendedQty * (Decimal)quantitiesAtLevel;}// Put the data in the field.pRow["ExtendedQuantity"] = extendedQty;}}else{// Update the "IsInEffectivity" field on this record.pRow["IsInEffectivity"] = 0;}}// Write out the summary BOM to the spreadsheet file.// Filter out the components that are not in effectivity and sort // the DataSet on the COMPONENT field.foundRows = componentsTable.Select("IsInEffectivity = 1", "COMPONENT, COM_TYP ASC");// Create a string for the criteria.string selectDemandRecords, selectInvRecords, selectCostRecords;// Create variables so we can summarize the records.string currentComponent = (foundRows[0]["COMPONENT"]).ToString();string currentComTyp = (foundRows[0]["COM_TYP"]).ToString();Decimal calculatedDemand = 0;Decimal quantityForComponent = 0;// Loop though the DataSet and write the summarized records to the xls file.//int startingExcelRowNumber = 1;//int currentExcelRowNumber = startingExcelRowNumber;for (int i = 0; i < foundRows.Length; i++){// If this record is the same component as the previous record, // summarize the quantity.if (currentComponent.Equals((foundRows["COMPONENT"]).ToString()) && currentComTyp.Equals((foundRows["COM_TYP"]).ToString())){quantityForComponent = quantityForComponent + Decimal.Parse((foundRows["ExtendedQuantity"]).ToString(), System.Globalization.NumberStyles.AllowThousands | System.Globalization.NumberStyles.AllowDecimalPoint | System.Globalization.NumberStyles.AllowLeadingWhite | System.Globalization.NumberStyles.AllowTrailingWhite | System.Globalization.NumberStyles.AllowLeadingSign);}// If this is a different component from the previous record,// write the previous component to the xls file.else{selectDemandRecords = "Item = '" + foundRows[i-1]["COMPONENT"].ToString() + "' AND "+ "COMP_STA < '5'" + " AND COM_TYP = '" + foundRows[i-1]["COM_TYP"].ToString() + "'";foundDemands = appDemandTable.Select(selectDemandRecords);for(int j = 0; j < foundDemands.Length; j++){// Add up the demand for this component.calculatedDemand += Decimal.Parse(foundDemands[j]["REQD_QTY"].ToString(), System.Globalization.NumberStyles.AllowThousands | System.Globalization.NumberStyles.AllowDecimalPoint | System.Globalization.NumberStyles.AllowLeadingWhite | System.Globalization.NumberStyles.AllowTrailingWhite | System.Globalization.NumberStyles.AllowLeadingSign) - Decimal.Parse(foundDemands[j]["ISSUED_QTY"].ToString(), System.Globalization.NumberStyles.AllowThousands | System.Globalization.NumberStyles.AllowDecimalPoint | System.Globalization.NumberStyles.AllowLeadingWhite | System.Globalization.NumberStyles.AllowTrailingWhite | System.Globalization.NumberStyles.AllowLeadingSign);}selectInvRecords = "Item = '" + foundRows[i-1]["COMPONENT"].ToString() + "'";foundInv = appInvTable.Select(selectInvRecords);selectCostRecords = "Item = '" + foundRows[i-1]["COMPONENT"].ToString() + "'";foundCosts = appCostTable.Select(selectCostRecords);foundRefDes = refDesTable.Select("Item = '" + foundRows[i-1]["COMPONENT"].ToString() + "' " + "AND [Parent] = '" + foundRows[i-1]["PARENT"].ToString() + "' " + "AND RD_IN_EFFECT < #" + effectiveDate.ToShortDateString() + "# "+ "AND RD_OUT_EFFECT > #" + effectiveDate.ToShortDateString() + "#");refDesignators = new SortedList();for(int m = 0; m < foundRefDes.Length; m++){refDesignators[foundRefDes[m]["RefDes"].ToString()] = foundRefDes[m]["RefDes"].ToString();}foreach (String s in refDesignators.Values){RefDesString = RefDesString + (RefDesString.Length>0 ? "," : "") + s;} foundItems = itemTable.Select("ITEM = '" + foundRows[i-1]["COMPONENT"].ToString() + "'");if (foundItems.Length > 0){//rangeCOMP_REV.Value2 = foundItems[0]["REV"].ToString();//rangeLOT_SZ_MULT.Value2 = foundItems[0]["LOT_SZ_MUL"].ToString();//outputStr.Append("<TD>=\""+foundItems[0]["REV"].ToString()+ "\"</TD>");//outputStr.Append("<TD>=\""+foundItems[0]["LOT_SZ_MUL"].ToString()+ "\"</TD>");}foundVendors = appVendTable.Select("Item = '" + foundRows[i-1]["COMPONENT"].ToString() + "'");if (foundVendors.Length == 0){//rangeSOURCE.Value2 = "None";//outputStr.Append("<TD>=\""+"None"+ "\"</TD>");string Sourcegroup = "None,Single,Multiple";string[] Sourcegroupname = Sourcegroup.Split(new char[] {','}); string Source = String.Join(".", Sourcegroupname, 0, 1);}if (foundVendors.Length == 1){//rangeSOURCE.Value2 = "Single";//outputStr.Append("<TD>=\""+"Single"+ "\"</TD>");string Sourcegroup = "None,Single,Multiple";string[] Sourcegroupname = Sourcegroup.Split(new char[] {','});string Source = String.Join(".", Sourcegroupname, 1, 1);}if (foundVendors.Length > 1){//rangeSOURCE.Value2 = "Multiple";//outputStr.Append("<TD>=\""+"Multiple"+ "\"</TD>");string Sourcegroup = "None,Single,Multiple";string[] Sourcegroupname = Sourcegroup.Split(new char[] {','});string Source = String.Join(".", Sourcegroupname, 2, 1);}avlNumbers = new SortedList();for(int n = 0; n < foundVendors.Length; n++){avlNumbers[foundVendors[n]["Vendor_ID"].ToString()] = foundVendors[n]["Vendor_ID"].ToString();}foreach (String s in avlNumbers.Values){avlString = avlString + (avlString.Length>0 ? "," : "") + s;}if (avlString.Length > 0){//rangeAVL_NUMS.Value2 = avlString;//outputStr.Append("<TD>=\""+avlString+ "\"</TD>");}for (int k = 0; k < foundCosts.Length; k++){// STD_COST if (Int32.Parse((foundCosts[k]["COST_CODE"].ToString())) == 4 && (Int32.Parse(foundCosts[k]["COST_TYPE"].ToString())) == 0){//rangeSTD_COST.Value2 = foundCosts[k]["TOT_ROLCST"];//outputStr.Append("<TD>=\""+foundCosts[k]["TOT_ROLCST"]+ "\"</TD>");}// Cost from Asiaif (Int32.Parse(foundCosts[k]["COST_TYPE"].ToString()) == 1 ||Int32.Parse(foundCosts[k]["COST_TYPE"].ToString()) == 3 ||Int32.Parse(foundCosts[k]["COST_TYPE"].ToString()) == 5){//rangeASIA_COST.Value2 = foundCosts[k]["TOT_ROLCST"];//outputStr.Append("<TD>=\""+foundCosts[k]["TOT_ROLCST"]+ "\"</TD>");}}if (foundInv.Length > 0){//OHND_QTY, INSP_QTY, ON_ORD_QTY//rangeOHND_QTY.Value2 = foundInv[0]["OHND_QTY"];//rangeINSP_QTY.Value2 = foundInv[0]["INSP_QTY"];//rangeON_ORD_QTY.Value2 = foundInv[0]["ON_ORD_QTY"];//rangeCOMP_MATL.Value2 = foundInv[0]["MATL"];//outputStr.Append("<TD>=\""+foundInv[0]["OHND_QTY"]+ "\"</TD>");//outputStr.Append("<TD>=\""+foundInv[0]["INSP_QTY"]+ "\"</TD>");//outputStr.Append("<TD>=\""+foundInv[0]["ON_ORD_QTY"]+ "\"</TD>");//outputStr.Append("<TD>=\""+foundInv[0]["MATL"]+ "\"</TD>");}quantityForComponent = Decimal.Parse((foundRows["ExtendedQuantity"]).ToString(), System.Globalization.NumberStyles.AllowThousands | System.Globalization.NumberStyles.AllowDecimalPoint | System.Globalization.NumberStyles.AllowLeadingWhite | System.Globalization.NumberStyles.AllowTrailingWhite | System.Globalization.NumberStyles.AllowLeadingSign);// Reset the demand.calculatedDemand = 0;}currentComponent = (foundRows["COMPONENT"]).ToString();currentComTyp = (foundRows["COM_TYP"]).ToString();RefDesString = "";avlString = "";}// Write the last record to the xls file.foundRefDes = refDesTable.Select("Item = '" + foundRows[foundRows.Length-1]["COMPONENT"].ToString() + "' " + "AND [Parent] = '" + foundRows[foundRows.Length-1]["PARENT"].ToString() + "' " + "AND RD_IN_EFFECT < #" + effectiveDate.ToShortDateString() + "# "+ "AND RD_OUT_EFFECT > #" + effectiveDate.ToShortDateString() + "#");refDesignators = new SortedList();for(int m = 0; m < foundRefDes.Length; m++){refDesignators[foundRefDes[m]["RefDes"].ToString()] = foundRefDes[m]["RefDes"].ToString();}foreach (String s in refDesignators.Values){RefDesString = RefDesString + (RefDesString.Length>0 ? "," : "") + s;}selectDemandRecords = "Item = '" + foundRows[foundRows.Length-1]["COMPONENT"].ToString() + "' AND "+ "COMP_STA < '5'" + " AND COM_TYP = '" + foundRows[foundRows.Length-1]["COM_TYP"].ToString() + "'";foundDemands = appDemandTable.Select(selectDemandRecords);for(int j = 0; j < foundDemands.Length; j++){// Add up the demand for this component.calculatedDemand += Decimal.Parse(foundDemands[j]["REQD_QTY"].ToString(), System.Globalization.NumberStyles.AllowThousands | System.Globalization.NumberStyles.AllowDecimalPoint | System.Globalization.NumberStyles.AllowLeadingWhite | System.Globalization.NumberStyles.AllowTrailingWhite | System.Globalization.NumberStyles.AllowLeadingSign) - Decimal.Parse(foundDemands[j]["ISSUED_QTY"].ToString(), System.Globalization.NumberStyles.AllowThousands | System.Globalization.NumberStyles.AllowDecimalPoint | System.Globalization.NumberStyles.AllowLeadingWhite | System.Globalization.NumberStyles.AllowTrailingWhite | System.Globalization.NumberStyles.AllowLeadingSign);}//currentExcelRowNumber++;//rangeEND_ITEM = worksheet.get_Range("A"+currentExcelRowNumber.ToString(), Missing.Value);//rangeCOMPONENT = worksheet.get_Range("B"+currentExcelRowNumber.ToString(), Missing.Value);//rangeCOMP_REV = worksheet.get_Range("C"+currentExcelRowNumber.ToString(), Missing.Value);//rangeCOMP_DESC = worksheet.get_Range("D"+currentExcelRowNumber.ToString(), Missing.Value);//rangeCOM_TYP = worksheet.get_Range("E"+currentExcelRowNumber.ToString(), Missing.Value);//rangeSUM_QTY = worksheet.get_Range("F"+currentExcelRowNumber.ToString(), Missing.Value);//rangeCOMP_UM = worksheet.get_Range("G"+currentExcelRowNumber.ToString(), Missing.Value);//rangeCOMP_MB = worksheet.get_Range("H"+currentExcelRowNumber.ToString(), Missing.Value);//rangeC_FIX_LT = worksheet.get_Range("I"+currentExcelRowNumber.ToString(), Missing.Value);//rangeC_INSP_LT = worksheet.get_Range("J"+currentExcelRowNumber.ToString(), Missing.Value);//rangeBuyer_Planner = worksheet.get_Range("K"+currentExcelRowNumber.ToString(), Missing.Value);//rangeCOMP_MATL = worksheet.get_Range("L"+currentExcelRowNumber.ToString(), Missing.Value);//rangeSTD_COST = worksheet.get_Range("M"+currentExcelRowNumber.ToString(), Missing.Value);//rangeCNTRACT_PRICE = worksheet.get_Range("N"+currentExcelRowNumber.ToString(), Missing.Value);//rangeASIA_COST = worksheet.get_Range("O"+currentExcelRowNumber.ToString(), Missing.Value);//rangeOHND_QTY = worksheet.get_Range("P"+currentExcelRowNumber.ToString(), Missing.Value);//rangeINSP_QTY = worksheet.get_Range("Q"+currentExcelRowNumber.ToString(), Missing.Value);//rangeInHouse = worksheet.get_Range("R"+currentExcelRowNumber.ToString(), Missing.Value);//rangeON_ORD_QTY = worksheet.get_Range("S"+currentExcelRowNumber.ToString(), Missing.Value);//rangeCUR_DMND1 = worksheet.get_Range("T"+currentExcelRowNumber.ToString(), Missing.Value);//rangeExcessInv = worksheet.get_Range("U"+currentExcelRowNumber.ToString(), Missing.Value);//rangeSOURCE = worksheet.get_Range("V"+currentExcelRowNumber.ToString(), Missing.Value);//rangeAVL_NUMS = worksheet.get_Range("W"+currentExcelRowNumber.ToString(), Missing.Value);//rangeITEM_REF1 = worksheet.get_Range("X"+currentExcelRowNumber.ToString(), Missing.Value);//rangePARENT_REV = worksheet.get_Range("Y"+currentExcelRowNumber.ToString(), Missing.Value);//rangeLOT_SZ_MULT = worksheet.get_Range("Z"+currentExcelRowNumber.ToString(), Missing.Value);//rangeEND_ITEM.Value2 = foundRows[foundRows.Length-1]["END_ITEM"];//rangeCOMPONENT.Value2 = foundRows[foundRows.Length-1]["COMPONENT"];//rangeCOMP_DESC.Value2 = foundRows[foundRows.Length-1]["COMP_DESC"];//rangePARENT_REV.Value2 = foundRows[foundRows.Length-1]["REV"];//rangeITEM_REF1.Value2 = RefDesString.Substring(0,Math.Min(255, Math.Max(RefDesString.Length-1, 0)));//rangeCOM_TYP.Value2 = foundRows[foundRows.Length-1]["COM_TYP"];//rangeCUR_DMND1.Value2 = Decimal.Round(calculatedDemand, 12);//rangeCOMP_MB.Value2 = foundRows[foundRows.Length-1]["COMP_MB"];//rangeBuyer_Planner.Value2 = foundRows[foundRows.Length-1]["BUYR"];//rangeCOMP_UM.Value2 = foundRows[foundRows.Length-1]["COMP_UM"];//rangeC_FIX_LT.Value2 = foundRows[foundRows.Length-1]["C_FIX_LT"];//rangeC_INSP_LT.Value2 = foundRows[foundRows.Length-1]["C_INSP_LT"];//rangeSUM_QTY.Value2 = Decimal.Round(quantityForComponent, 12);//outputStr.Append("<TD>=\""+foundRows[foundRows.Length-1]["END_ITEM"]+ "\"</TD>");//outputStr.Append("<TD>=\""+foundRows[foundRows.Length-1]["COMPONENT"]+ "\"</TD>");//outputStr.Append("<TD>=\""+foundRows[foundRows.Length-1]["COMP_DESC"]+ "\"</TD>");//outputStr.Append("<TD>=\""+foundRows[foundRows.Length-1]["REV"]+ "\"</TD>");//outputStr.Append("<TD>=\""+RefDesString.Substring(0,Math.Min(255, Math.Max(RefDesString.Length-1, 0)))+ "\"</TD>");//outputStr.Append("<TD>=\""+foundRows[foundRows.Length-1]["COM_TYP"]+ "\"</TD>");//outputStr.Append("<TD>=\""+Decimal.Round(calculatedDemand, 12)+ "\"</TD>");//outputStr.Append("<TD>=\""+foundRows[foundRows.Length-1]["COMP_MB"]+ "\"</TD>");//outputStr.Append("<TD>=\""+foundRows[foundRows.Length-1]["BUYR"]+ "\"</TD>");//outputStr.Append("<TD>=\""+foundRows[foundRows.Length-1]["COMP_UM"]+ "\"</TD>");//outputStr.Append("<TD>=\""+foundRows[foundRows.Length-1]["C_FIX_LT"]+ "\"</TD>");//outputStr.Append("<TD>=\""+foundRows[foundRows.Length-1]["C_INSP_LT"]+ "\"</TD>");//outputStr.Append("<TD>=\""+Decimal.Round(quantityForComponent, 12)+ "\"</TD>");foundItems = itemTable.Select("ITEM = '" + foundRows[foundRows.Length-1]["COMPONENT"].ToString() + "'");if (foundItems.Length > 0){//rangeCOMP_REV.Value2 = foundItems[0]["REV"].ToString();//rangeLOT_SZ_MULT.Value2 = foundItems[0]["LOT_SZ_MUL"].ToString();//outputStr.Append("<TD>=\""+foundItems[0]["REV"].ToString()+ "\"</TD>");//outputStr.Append("<TD>=\""+foundItems[0]["LOT_SZ_MUL"].ToString()+ "\"</TD>");}foundVendors = appVendTable.Select("Item = '" + foundRows[foundRows.Length-1]["COMPONENT"].ToString() + "'");if (foundVendors.Length == 0){//rangeSOURCE.Value2 = "None";//outputStr.Append("<TD>=\""+"None"+ "\"</TD>");}if (foundVendors.Length == 1){//rangeSOURCE.Value2 = "Single";//outputStr.Append("<TD>=\""+"Single"+ "\"</TD>");}if (foundVendors.Length > 1){//rangeSOURCE.Value2 = "Multiple";//outputStr.Append("<TD>=\""+"Multiple"+ "\"</TD>");}avlNumbers = new SortedList();for(int n = 0; n < foundVendors.Length; n++){avlNumbers[foundVendors[n]["Vendor_ID"].ToString()] = foundVendors[n]["Vendor_ID"].ToString();}foreach (String s in avlNumbers.Values){avlString = avlString + (avlString.Length>0 ? "," : "") + s;}if (avlString.Length > 0){//rangeAVL_NUMS.Value2 = avlString;//outputStr.Append("<TD>=\""+avlString+ "\"</TD>");}selectInvRecords = "Item = '" + foundRows[foundRows.Length-1]["COMPONENT"].ToString() + "'";foundInv = appInvTable.Select(selectInvRecords);if (foundInv.Length > 0){//OHND_QTY, INSP_QTY, ON_ORD_QTY//rangeOHND_QTY.Value2 = foundInv[0]["OHND_QTY"];//rangeINSP_QTY.Value2 = foundInv[0]["INSP_QTY"];//rangeON_ORD_QTY.Value2 = foundInv[0]["ON_ORD_QTY"];//rangeCOMP_MATL.Value2 = foundInv[0]["MATL"];//outputStr.Append("<TD>=\""+foundInv[0]["OHND_QTY"]+ "\"</TD>");//outputStr.Append("<TD>=\""+foundInv[0]["INSP_QTY"]+ "\"</TD>");//outputStr.Append("<TD>=\""+foundInv[0]["ON_ORD_QTY"]+ "\"</TD>");//outputStr.Append("<TD>=\""+foundInv[0]["MATL"]+ "\"</TD>");}selectCostRecords = "Item = '" + foundRows[foundRows.Length-1]["COMPONENT"].ToString() + "'";foundCosts = appCostTable.Select(selectCostRecords);for (int k = 0; k < foundCosts.Length; k++){//SELECT ITEM, COST_CODE, COST_TYPE, MATL, TOT_ROLCST if (Int32.Parse((foundCosts[k]["COST_CODE"].ToString())) == 4 && (Int32.Parse(foundCosts[k]["COST_TYPE"].ToString())) == 0){//rangeSTD_COST.Value2 = foundCosts[k]["TOT_ROLCST"];//outputStr.Append("<TD>=\""+foundCosts[k]["TOT_ROLCST"]+ "\"</TD>");}if (Int32.Parse(foundCosts[k]["COST_TYPE"].ToString()) == 1 ||Int32.Parse(foundCosts[k]["COST_TYPE"].ToString()) == 3 ||Int32.Parse(foundCosts[k]["COST_TYPE"].ToString()) == 5){//rangeASIA_COST.Value2 = foundCosts[k]["TOT_ROLCST"];//outputStr.Append("<TD>=\""+foundCosts[k]["TOT_ROLCST"]+ "\"</TD>");}}foreach (DataRow pRow in mlBomDS.Tables["ITEM_MULTILEVELBILL"].Rows) // for(int i=0; i < dv.Count; i++) {outputStr.Append("<TR>"); outputStr.Append("<TD>=\"" + pRow["END_ITEM"].ToString() + "\"</TD>");outputStr.Append("<TD>" + pRow["COMPONENT"].ToString() + "</TD>");outputStr.Append("<TD>=\"" + pRow["REV"].ToString() + "\"</TD>");outputStr.Append("<TD>" + pRow["COMP_DESC"].ToString() + "</TD>");outputStr.Append("<TD>" + pRow["COM_TYP"].ToString() + "</TD>");outputStr.Append("<TD>=\""+Decimal.Round(quantityForComponent, 12)+ "\"</TD>");outputStr.Append("<TD>=\"" + pRow["COMP_UM"].ToString() + "\"</TD>");outputStr.Append("<TD>=\"" + pRow["COMP_MB"].ToString() + "\"</TD>");outputStr.Append("<TD>=\"" + pRow["C_FIX_LT"].ToString() + "\"</TD>");outputStr.Append("<TD>" + pRow["C_INSP_LT"].ToString() + "</TD>");outputStr.Append("<TD>" + pRow["BUYR"].ToString() + "</TD>");outputStr.Append("<TD>=\""+foundInv[0]["MATL"]+ "\"</TD>");outputStr.Append("<TD>=\""+foundCosts[0]["TOT_ROLCST"]+ "\"</TD>");//outputStr.Append("<TD>=\"" + pRow["CNTRACT_PRICE"].ToString() + "\"</TD>");outputStr.Append("<TD>=\"" + "" + "\"</TD>");outputStr.Append("<TD>=\""+foundCosts[0]["TOT_ROLCST"]+ "\"</TD>");outputStr.Append("<TD>=\"" + pRow["OHND_QTY"].ToString() +"\"</TD>");outputStr.Append("<TD>=\"" + pRow["INSP_QTY"].ToString() + "\"</TD>");//outputStr.Append("<TD>" + pRow["INHOUSE"].ToString() + "</TD>");outputStr.Append("<TD>" + "" + "</TD>");outputStr.Append("<TD>=\"" + pRow["ON_ORD_QTY"].ToString() + "\"</TD>");outputStr.Append("<TD>=\""+Decimal.Round(calculatedDemand, 12)+ "\"</TD>");//outputStr.Append("<TD>=\""+RefDesString.Substring(0,Math.Min(255, Math.Max(RefDesString.Length-1, 0)))+ "\"</TD>");outputStr.Append("<TD>" + "" + "</TD>"); outputStr.Append("<TD>=\""+ "" + "\"</TD>"); outputStr.Append("<TD>=\""+"" + "\"</TD>");outputStr.Append("<TD>=\""+RefDesString.Substring(0,Math.Min(255, Math.Max(RefDesString.Length-1, 0)))+ "\"</TD>");outputStr.Append("<TD>=\""+foundRows[foundRows.Length-1]["REV"]+ "\"</TD>");outputStr.Append("<TD>=\""+foundItems[0]["LOT_SZ_MUL"].ToString()+ "\"</TD>");////outputStr.Append("<TD>=\"" + pRow["SOURCE"].ToString() + "\"</TD>");outputStr.Append("</TR>");}//app.DisplayAlerts = false;//workbook.SaveAs(dataDir + Request.QueryString["EndItem"].Trim() + "BOMRisk.xls", //Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, //Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, //Missing.Value, Missing.Value, Missing.Value);//workbook.Saved = true;//workbook.Close(true, dataDir + Request.QueryString["EndItem"].Trim() + "BOMRisk.xls", false);string sessionVarName = "BOMRiskReport" + DateTime.Now.ToString("yyyyMMddHHmmss"); Session[sessionVarName] = outputStr ;// Redirect the browser to an aspx page that can render // as an Excel spreadsheet the HTML string we just created.string fullURL = Request.Url.ToString();string server = fullURL.Substring(0, fullURL.IndexOf(Request.RawUrl.ToString().Substring(0,8)));((Power)Page).redirectURL = server + "/CelesticaPower/ExcelData.aspx?Data=" + sessionVarName;((Power)Page).redirectTarget = "_blank";((Power)Page).redirectFeatures = "location=yes, directories=no, toolbar=no, menubar=yes, resizable=yes, scrollbars=yes";((Power)Page).redirectReplace = false;ResultLabel.Text = "BOMRiskAnalysis Report created.";// Redirect the browser to the spreadsheet xls file we just created and populated.//string fullURL = Request.Url.ToString();//string server = fullURL.Substring(0, fullURL.IndexOf(Request.RawUrl.ToString().Substring(0,8)));//((Power)Page).redirectURL = server + "/BOM/" //+ Request.QueryString["EndItem"].Trim() + "BOMRisk.xls";//((Power)Page).redirectTarget = "_blank";//((Power)Page).redirectFeatures = "location=yes, directories=no, toolbar=no, menubar=yes, resizable=yes, scrollbars=yes";//((Power)Page).redirectReplace = false;//ResultLabel.Text = Request.QueryString["EndItem"].Trim() + " Demand spreadsheet created.";}else{ResultLabel.Text = "There are no components in the database for the end item: " + Request.QueryString["EndItem"];}}catch (Exception except){ErrorLabel.Text = except.Message;ErrorLabel.Visible = true;}//finally//{//if (app != null)//{//app.UserControl = false;//app.Quit();//app = null;//}//}}}}#region Web Form Designer generated codeoverride protected void OnInit(EventArgs e){//// CODEGEN: This call is required by the ASP.NET Web Form Designer.//InitializeComponent();base.OnInit(e);}///Required method for Designer support - do not modify///the contents of this method with the code editor./// </summary>private void InitializeComponent(){this.Load += new System.EventHandler(this.Page_Load);}#endregion}} September 7, 2005 at 8:00 am
This was removed by the editor as SPAM
September 8, 2005 at 1:50 pm
Prevoiusly I posted the same doubt and got few suggestions. I tried them but did not help.
Any way I could find a way to append the string value and now I get the output the way I want.
Thanks Everyone!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply