private void BindGridOnRetriveButtonClick() { int suppSel = 0; string[] arrySuppName = new string[] { "", "", "", "", "", "", "", "", "", "" }; string[] arrSuppNameInShort = new string[10]; ViewState["suppliercode"] = ""; ViewState["quotationcode"] = ""; ViewState["suppcurrency"] = ""; string SuppQtnCodesItemTypes = ""; if (rgdQuatationInfo.MasterTableView.Columns.Count > 12) { //Remove all the programatically generated colomn from grid and for (int i = rgdQuatationInfo.MasterTableView.Columns.Count - 1; i >= 12; i--) { rgdQuatationInfo.MasterTableView.Columns.RemoveAt(i); } } if (Request.QueryString["Requisitioncode"].ToString().Substring(0, 2) == "ST" || Request.QueryString["Requisitioncode"].ToString().Substring(0, 3) == "OST") { rgdQuatationInfo.MasterTableView.Columns[3].Display = false; } StringBuilder strSql = new StringBuilder(); StringBuilder strTables = new StringBuilder(); decimal MinValues = 0; int col_id = 11; int sel_Id = 0; string supplist = ""; // HiddenChepSupp.Value = 0; // strSql.Append("select distinct a.ITEM_SERIAL_NO,'False' chechstatus, a.QUOTATION_CODE,a.ITEM_REF_CODE, a.ITEM_SHORT_DESC,a.ITEM_FULL_DESC,a.QUOTED_UNIT_ID,a.QUOTED_QTY"); strSql.Append(@"select distinct a.ITEM_SERIAL_NO ,'False' chechstatus , a.QUOTATION_CODE ,a.ITEM_REF_CODE , a.ITEM_SHORT_DESC ,a.ITEM_FULL_DESC ,a.QUOTED_QTY ,case when item.Drawing_Number='0' then '' else item.Drawing_Number end Drawing_Number ,Item.Part_Number,isnull(Item.Long_Description,'') Long_Description , M.ITEM_COMMENT ,item.Unit_and_Packings ,M.ROB_Qty ,M.REQUESTED_QTY ,M.ORDER_QTY ,isnull(M.ITEM_INTERN_REF,0) as ITEM_INTERN_REF ,a.Vessel_Code ,PURC_LIB_SUBSYSTEMS.Subsystem_Description ,'" + lblCatalog.Text.Trim() + @"' as Catalogue , '" + lblReqNo.Text.Trim() + "' as Reqsnno"); // strTables.Append(" from PURC_Dtl_Quoted_Prices a "); strTables.Append(@" from PURC_Dtl_Quoted_Prices a inner join PURC_Lib_Items item On item.Item_Intern_Ref=A.Item_Ref_Code inner join PURC_Dtl_Supply_Items M on M.item_ref_code=A.item_ref_code and M.Document_Code=a.Document_Code and a.Vessel_Code=M.Vessel_Code inner join PURC_LIB_SUBSYSTEMS on PURC_LIB_SUBSYSTEMS.Subsystem_Code =M.ITEM_SUBSYSTEM_CODE and PURC_LIB_SUBSYSTEMS.System_Code=M.ITEM_SYSTEM_CODE "); string BackColor = ""; int suppCountBG = 2; foreach (GridDataItem dataItem in rgdSupplierInfo.MasterTableView.Items) { string str = ""; TextBox txtgrdItemReqQty = (TextBox)(dataItem.FindControl("txtgrdItemReqstdQty") as TextBox); CheckBox chk = (CheckBox)(dataItem.FindControl("chkQuaEvaluated") as CheckBox); string PortName = rgdSupplierInfo.MasterTableView.DataKeyValues[dataItem.ItemIndex]["PortName"].ToString(); string suppcurrency = rgdSupplierInfo.MasterTableView.DataKeyValues[dataItem.ItemIndex]["Currency"].ToString(); if ((chk.Checked)) { if (suppCountBG % 2 == 0)// assigne the different color to suppliers { BackColor = "QtnEval-ItemStyle-css"; } else { BackColor = "QtnEval-AltItemStyle-css"; } suppCountBG++; col_id = col_id + Convert.ToInt32(ViewState["ColumnCount_Supp"].ToString()); string QUOTATION_CODE = dataItem["QUOTATION_CODE"].Text.ToString(); string Col_supp = dataItem["SUPPLIER"].Text.ToString().Replace('-', '_') + QUOTATION_CODE.Replace('-', '_'); string Col_supp_Alias = "Supp" + dataItem["SUPPLIER"].Text.ToString().Trim() + dataItem.ItemIndex.ToString(); string Col_supp_where = dataItem["SUPPLIER"].Text; string Col_supp_Short = dataItem["SHORT_NAME"].Text.ToString(); string strColSupp = ""; if (Col_supp_Short.Length > Convert.ToInt32(ViewState["ColumnCount_Supp"].ToString())) { for (int i = 0; i < Convert.ToInt32(ViewState["ColumnCount_Supp"].ToString()); i++) { strColSupp = strColSupp + Col_supp_Short[i]; } } else { strColSupp = Col_supp_Short; } strSql.Append(","); strTables.Append(" Inner Join "); str = @"(select supl.ITEM_REF_CODE ,( QUOTED_RATE * " + dataItem["EXCHANGE_RATE"].Text.ToString() + ") " + Col_supp_Alias + @"_Rate ,QUOTED_PRICE " + Col_supp_Alias + @"_Price ,QUOTED_DISCOUNT " + Col_supp_Alias + @"_Discount , QUOTATION_REMARKS " + Col_supp_Alias + @"_Remark , case when isnull(EVALUATION_OPTION,0)=1 then 'True' else 'False' end as " + Col_supp_Alias + @"_Status ,QUOTATION_CODE ,(((cast(QUOTED_RATE*" + dataItem["EXCHANGE_RATE"].Text.Trim() + " as decimal(18,2))*supl.ORDER_QTY)-(cast(QUOTED_RATE*" + dataItem["EXCHANGE_RATE"].Text.Trim() + " as decimal(18,2))*supl.ORDER_QTY*cast(QUOTED_DISCOUNT* " + dataItem["EXCHANGE_RATE"].Text.Trim() + " as decimal(18,2))/100))) " + Col_supp_Alias + @"_Amount , isnull(Lead_Time,'') " + Col_supp_Alias + @"_Lead_Time , [Description]" + Col_supp_Alias + @"_ItemType from PURC_Dtl_Quoted_Prices inner join PURC_DTL_SUPPLY_ITEMS supl on supl.DOCUMENT_CODE=PURC_Dtl_Quoted_Prices.DOCUMENT_CODE and supl.ITEM_REF_CODE=PURC_Dtl_Quoted_Prices.ITEM_REF_CODE inner join PURC_LIB_SYSTEM_PARAMETERS on Code=Item_Type where supplier_code='" + Col_supp_where + "' and QUOTATION_CODE ='" + QUOTATION_CODE + "') " + Col_supp_Alias + " on " + Col_supp_Alias + ".ITEM_REF_CODE = a.ITEM_REF_CODE "; if (col_id == 18) { str += " and " + Col_supp_Alias + ".QUOTATION_CODE=a.QUOTATION_CODE "; } strTables.Append(str); strSql.Append(Col_supp_Alias); strSql.Append(".* "); // strSql.Append(" (select isnull(Description,'Original')as Description from PURC_LIB_SYSTEM_PARAMETERS where Code=isnull(a.Item_Type,154) ) as ItemType "); GridBoundColumn boundColumn; boundColumn = new GridBoundColumn(); boundColumn.HeaderText = "Unit Price"; boundColumn.DataField = Col_supp_Alias + "_Rate"; boundColumn.UniqueName = Col_supp + "_Rate"; boundColumn.DataFormatString = "{0:F2}"; boundColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right; boundColumn.ItemStyle.CssClass = BackColor; boundColumn.HeaderStyle.Width = 70; boundColumn.ItemStyle.Width = 70; rgdQuatationInfo.MasterTableView.Columns.Add(boundColumn); boundColumn = new GridBoundColumn(); boundColumn.HeaderText = "Discount"; boundColumn.DataField = Col_supp_Alias + "_Discount"; boundColumn.UniqueName = Col_supp + "_Discount"; boundColumn.DataFormatString = "{0:F2}"; boundColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right; boundColumn.ItemStyle.CssClass = BackColor; boundColumn.HeaderStyle.Width = 70; boundColumn.ItemStyle.Width = 70; rgdQuatationInfo.MasterTableView.Columns.Add(boundColumn); boundColumn = new GridBoundColumn(); boundColumn.HeaderText = "Amount"; boundColumn.DataField = Col_supp_Alias + "_Amount"; boundColumn.UniqueName = Col_supp + "_Amount"; boundColumn.DataFormatString = "{0:F2}"; boundColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right; boundColumn.ItemStyle.CssClass = BackColor; boundColumn.HeaderStyle.Width = 70; boundColumn.ItemStyle.Width = 70; rgdQuatationInfo.MasterTableView.Columns.Add(boundColumn); boundColumn = new GridBoundColumn(); boundColumn.HeaderText = "Lead days"; boundColumn.DataField = Col_supp_Alias + "_Lead_Time"; boundColumn.UniqueName = Col_supp + "_Lead_Time"; // boundColumn.DataFormatString = "{0:F2}"; boundColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right; boundColumn.ItemStyle.CssClass = BackColor; boundColumn.HeaderStyle.Width = 70; boundColumn.ItemStyle.Width = 70; rgdQuatationInfo.MasterTableView.Columns.Add(boundColumn); boundColumn = new GridBoundColumn(); boundColumn.HeaderText = "ItemType"; boundColumn.DataField = Col_supp_Alias + "_ItemType"; boundColumn.UniqueName = Col_supp + "_ItemType"; boundColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right; boundColumn.ItemStyle.CssClass = BackColor; boundColumn.HeaderStyle.Width = 70; boundColumn.ItemStyle.Width = 70; rgdQuatationInfo.MasterTableView.Columns.Add(boundColumn); GridTemplateColumn templateColumnRemark = new GridTemplateColumn(); templateColumnRemark.HeaderText = "Remark"; templateColumnRemark.DataField = Col_supp_Alias + "_Remark"; templateColumnRemark.ItemTemplate = new DataGridTemplateImage(ListItemType.Item, Col_supp, Col_supp + "_Remark"); templateColumnRemark.UniqueName = Col_supp + "_img"; templateColumnRemark.ItemStyle.HorizontalAlign = HorizontalAlign.Center; templateColumnRemark.ItemStyle.CssClass = BackColor; templateColumnRemark.ItemStyle.Width = 70; templateColumnRemark.HeaderStyle.Width = 70; rgdQuatationInfo.MasterTableView.Columns.Add(templateColumnRemark); GridTemplateColumn templateColumn = new GridTemplateColumn(); templateColumn.UniqueName = "TempChk"; templateColumn.HeaderTemplate = new DataGridTempla(ListItemType.Header, Col_supp, "Select", ""); templateColumn.ItemTemplate = new DataGridTempla(ListItemType.Item, Col_supp, Col_supp, Col_supp_Alias + "_Status"); templateColumn.UniqueName = Col_supp + "_chk"; templateColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Center; templateColumn.ItemStyle.CssClass = BackColor; templateColumn.HeaderStyle.Width = 70; templateColumn.ItemStyle.Width = 70; rgdQuatationInfo.MasterTableView.Columns.Add(templateColumn); ViewState["Col_supp"] = Col_supp; ViewState["Portname"] += PortName + ","; ViewState["suppcurrency"] += suppcurrency + ","; ViewState["suppliercode"] = ViewState["suppliercode"] + Col_supp_Alias + ","; ViewState["quotationcode"] = ViewState["quotationcode"] + QUOTATION_CODE + ","; SuppQtnCodesItemTypes += " select '" + QUOTATION_CODE + "' "; arrySuppName[suppSel] = Col_supp; supplist = Col_supp + "," + supplist; arrSuppNameInShort[suppSel] = strColSupp; suppSel += 1; if (MinValues == 0 || MinValues > Convert.ToDecimal(dataItem["Supp_Tot_Amt"].Text.ToString())) { //HiddenChepSupp.Value = Col_supp + "_chk"; HiddenChepSupp.Value = Col_supp; MinValues = Convert.ToDecimal(dataItem["Supp_Tot_Amt"].Text.ToString()); sel_Id = col_id; } } } strSql.Append(" "); strSql.Append(strTables); Session["SubQuerry"] = strSql.ToString(); strSql.Append("where a.Document_code ='" + Request.QueryString["Document_Code"].ToString() + "' and a.active_status=1 order by a.ITEM_SERIAL_NO"); SuppQtnCodesItemTypes = SuppQtnCodesItemTypes.Remove(SuppQtnCodesItemTypes.Length - 1, 1); dtItemsTypes = BLL_PURC_Common.GET_ItemTypeAll(SuppQtnCodesItemTypes); DataTable dt = new DataTable(); DataTable dtQuatationInfo = new DataTable(); ViewState["SuppSelforEval"] = suppSel; ViewState["supplierList"] = arrySuppName; ViewState["supplierListInShort"] = arrSuppNameInShort; ViewState["EvaluateTable"] = dtQuatationInfo; count = 0; // optEval.Attributes.Add("Onclick", "return CalculateByEvalOpt('" + EvalOpt + "'," +sel_Id+");"); Session["supplist"] = supplist.ToString(); TechnicalBAL objtechBAL = new TechnicalBAL(); string FinalQuery = strSql.ToString(); dtQuatationInfo = objtechBAL.GetTable(FinalQuery); rgdQuatationInfo.DataSource = dtQuatationInfo; rgdQuatationInfo.DataBind(); Session["QuatationInfo"] = dtQuatationInfo; Session["GeneratedQuery"] = FinalQuery; foreach (GridDataItem Item in rgdQuatationInfo.MasterTableView.Items) { int suppCount = (int)ViewState["SuppSelforEval"]; string[] arrSupp = (string[])ViewState["supplierList"]; count++; Label longDescpt = (Label)Item.FindControl("lblLongDesc"); if (longDescpt.ToolTip == "1") { ((HyperLink)Item.FindControl("lblItemDesc")).CssClass = "NewItem"; Item.Cells[8].BackColor = System.Drawing.Color.Yellow; } } ViewState["EvaluateTable"] = dtQuatationInfo; int column = 14; int PortCount = 0; foreach (string supp in arrSuppNameInShort) { if (supp != "" && supp != null) { info.AddMergedColumns(new int[] { column, column + 1, column + 2, column + 3, column + 4, column + 5, column + 6 }, supp + " (Port : " + ViewState["Portname"].ToString().Split(new char[] { ',' })[PortCount].ToString() + " ,Quoted Currency : " + ViewState["suppcurrency"].ToString().Split(new char[] { ',' })[PortCount].ToString() + ")"); column += Convert.ToInt32(ViewState["ColumnCount_Supp"].ToString()); } PortCount++; } }
//private void BindGridOnRetriveButtonClick(string Querry) //{ // string[] SupplierList = Querry.Split(','); //} private void BindGridOnRetriveButtonClick(string Querry) { // int suppSel = 0; string[] arrySuppName = new string[10]; string[] arrSuppNameInShort = new string[10]; string[] SupplierList = Querry.Split(','); //if (rgdItmSpecView.MasterTableView.Columns.Count > 10) //{ // //Remove all the programatically generated colomn from grid and // for (int i = rgdItmSpecView.MasterTableView.Columns.Count - 1; i >= 10; i--) // { // rgdItmSpecView.MasterTableView.Columns.RemoveAt(i); // } //} //if (Request.QueryString["Requisitioncode"].ToString().Substring(0, 2) == "ST" || Request.QueryString["Requisitioncode"].ToString().Substring(0, 3) == "OST") //{ // rgdItmSpecView.MasterTableView.Columns[3].Display = false; //} StringBuilder strSql = new StringBuilder(); StringBuilder strTables = new StringBuilder(); // decimal MinValues = 0; // int col_id = 9; // int sel_Id = 0; strSql.Append("select distinct a.ITEM_SERIAL_NO,'False' chechstatus, a.QUOTATION_CODE,a.ITEM_REF_CODE, a.ITEM_SHORT_DESC,a.ITEM_FULL_DESC,a.QUOTED_QTY,item.Drawing_Number,Item.Part_Number,item.Unit_and_Packings,M.ROB_Qty,M.REQUESTED_QTY"); // strTables.Append(" from PMS_Dtl_Quoted_Prices a "); strTables.Append(" from PURC_Dtl_Quoted_Prices a inner join PURC_Lib_Items item On item.Id=A.Item_Ref_Code inner join PURC_Dtl_Supply_Items M on M.item_ref_code=A.item_ref_code and M.Document_Code=a.Document_Code and a.Vessel_Code=M.Vessel_Code "); //foreach (GridDataItem dataItem in rgdSupplierInfo.MasterTableView.Items) //{ for (int i = 0; i < SupplierList.Length - 1; i++) { string Col_supp = SupplierList[i].ToString(); strSql.Append(","); strTables.Append(" Inner Join "); string str = "(select ITEM_REF_CODE,QUOTATION_CODE,isnull(OFFERED_QTY,0) " + Col_supp + "_OFFERED_QTY,isnull((select top 1 Description from PURC_Lib_System_Parameters where Parent_Type='153' and Short_Code=isnull(PURC_Dtl_Quoted_Prices.Item_Type,'ORG')),'') " + Col_supp + "_Item_Type from PURC_Dtl_Quoted_Prices where supplier_code='" + Col_supp + "' ) " + Col_supp + " on " + Col_supp + ".ITEM_REF_CODE = a.ITEM_REF_CODE and " + Col_supp + ".QUOTATION_CODE=a.QUOTATION_CODE "; strTables.Append(str); strSql.Append(Col_supp); strSql.Append(".* "); //GridBoundColumn boundColumn; //boundColumn = new GridBoundColumn(); //boundColumn.HeaderText = Col_supp + " " + "QUOTED_QTY"; //boundColumn.DataField = Col_supp + "_QUOTED_QTY"; //boundColumn.UniqueName = Col_supp + "_QUOTED_QTY"; //boundColumn.DataFormatString = "{0:F2}"; //boundColumn.MaxLength = 100; //boundColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right; //rgdItmSpecView.MasterTableView.Columns.Add(boundColumn); //boundColumn = new GridBoundColumn(); //boundColumn.HeaderText = Col_supp + " " + "Item_Type"; //boundColumn.DataField = Col_supp + "_Item_Type"; //boundColumn.UniqueName = Col_supp + "_Item_Type"; ////boundColumn.DataFormatString = "{0:F2}"; //boundColumn.MaxLength = 100; //boundColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Left; //rgdItmSpecView.MasterTableView.Columns.Add(boundColumn); } strSql.Append(" "); strSql.Append(strTables); //Session["SubQuerry"] = strSql.ToString(); string sqlstring = Session["SqlString"].ToString(); string [] queryString = sqlstring.Split(','); strSql.Append("where a.QUOTATION_CODE=(select top 1 QUOTATION_CODE from dbo.PURC_Dtl_Reqsn where REQUISITION_CODE='"); strSql.Append(queryString[0].ToString()); strSql.Append("'and Line_type='Q')"); strSql.Append("and a.Vessel_code ='" + queryString[1].ToString()); strSql.Append("'and a.Document_code ='" + queryString[2].ToString() + "'"); TechnicalBAL objtechBAL = new TechnicalBAL(); string FinalQuery = strSql.ToString(); DataTable dtQuatationInfo = objtechBAL.GetTable(FinalQuery); rgdItmSpecView.DataSource = dtQuatationInfo; rgdItmSpecView.DataBind(); }