protected void BtnSearch_Click(object sender, EventArgs e) { bool b = ValidateSearch(); if (b == true) { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); try { string query = "ACX_USP_OpenSaleOrder"; List <string> ilist = new List <string>(); List <string> item = new List <string>(); DataTable dt = new DataTable(); if (ddlSiteId.SelectedItem.Text == "") { ilist.Add("@Site_Code"); item.Add(Session["SiteCode"].ToString()); } else { ilist.Add("@Site_Code"); item.Add(ddlSiteId.SelectedItem.Value.ToString()); } ilist.Add("@DATAAREAID"); item.Add(Session["DATAAREAID"].ToString()); ilist.Add("@StartDate"); item.Add(txtFromDate.Text); ilist.Add("@EndDate"); item.Add(txtToDate.Text); dt = obj.GetData_New(query, CommandType.StoredProcedure, ilist, item); //string FromDate = txtFromDate.Text; //string ToDate = txtToDate.Text; if (dt.Rows.Count > 0) { gvDetails.DataSource = dt; gvDetails.DataBind(); LblMessage.Text = "Total Records : " + dt.Rows.Count.ToString(); gvDetails.Visible = true; Session["OpenSO"] = dt; } else { LblMessage.Text = string.Empty; gvDetails.DataSource = dt; gvDetails.DataBind(); //this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('No Data Exits Between This Date Range !');", true); LblMessage.Text = "No Data Exits Between This Date Range ! !"; LblMessage.Visible = true; uppanel.Update(); } } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } } }
//protected void txtAmount_KeyUp(object sender, EventArgs e) //{ // TextBox txtAmt = (TextBox)sender; // GridViewRow row = (GridViewRow)txtAmt.Parent.Parent; // // int nRow = row.RowIndex; // double amount = Convert.ToDouble(txtAmt.Text); // double PendingAmount = Convert.ToDouble(gvDetails.Rows[nRow].Cells[9].Text); // // if (amount > PendingAmount) // { // txtAmt.Text = string.Empty; // //gvDetails.Rows[nRow].Cells[10].Text = ""; // this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Entered amount cannot be greater than remaining amount');", true); // //this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Payment Entry Document No : Generated Successfully.!');", true); // } // else // { // // } //} protected void gvDetails_RowDataBound(object sender, GridViewRowEventArgs e) { try { if (e.Row.RowType == DataControlRowType.DataRow) { //============ Fill Data to dropdown of Gridview=================== DropDownList ddList = (DropDownList)e.Row.FindControl("drpInstrument"); DataTable dt = new DataTable(); List <string> ilist = new List <string>(); List <string> litem = new List <string>(); string query; //==========Instrument query = "Select [Instrument_Code],[Instrument_Description] from ax.ACXINSTRUMENTMASTER where DATAAREAID ='" + Session["DATAAREAID"].ToString() + "' and Blocked = 0 order by [Instrument_Description]"; dt = baseObj.GetData_New(query, CommandType.Text, ilist, litem); ddList.DataSource = dt; ddList.DataTextField = "Instrument_Description"; ddList.DataValueField = "Instrument_Code"; ddList.DataBind(); ddList.Items.Insert(0, new ListItem("--Select--", "0")); //==========Ref Doc Number //HiddenField plantcode = (HiddenField)e.Row.FindControl("HiddenField2"); //DropDownList ddRefNo = (DropDownList)e.Row.FindControl("drpRefDocument"); //dt = new DataTable(); // ////query = " Select VND.[Document_No] from ax.ACXVENDORTRANS VND WHERE VND.RefNo_DocumentNo is null and VND.[Document_No] not in (select ax.ACXVENDORTRANS.RefNo_DocumentNo from ax.ACXVENDORTRANS where ax.ACXVENDORTRANS.RefNo_DocumentNo is not null) " + //// " and VND.RemainingAmount<>0 and VND.[SiteCode]='" + Session["SiteCode"].ToString() + "' and vnd.[Dealer_Code]='" + plantcode.Value + "' " + //// " order by VND.[Document_No] desc "; //query = @"SELECT T.[Document_No] FROM ax.ACXVENDORTRANS T LEFT JOIN AX.ACXVENDORTRANS TR // ON T.DOCUMENT_NO=TR.REFNO_DOCUMENTNO AND T.SITECODE=TR.SITECODE // AND T.Dealer_Code=TR.Dealer_Code AND TR.DOCUMENTTYPE=2 AND T.DOCUMENTTYPE=1 // WHERE T.DOCUMENTTYPE=1 AND (ISNULL(TR.RemainingAmount,0)+T.RemainingAmount)>0 // AND T.SITECODE='" + Session["SiteCode"].ToString() + "' AND T.Dealer_Code='" + plantcode.Value + "' order by t.Document_No desc"; // //dt = baseObj.GetData_New(query, CommandType.Text, ilist, litem); // // //if (dt.Rows.Count > 0) //{ // ddRefNo.DataSource = dt; // ddRefNo.DataTextField = "Document_No"; // ddRefNo.DataValueField = "Document_No"; // ddRefNo.DataBind(); //} //ddRefNo.Items.Insert(0, new ListItem("--Select--", "0")); } } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('" + ex.Message.ToString().Replace("'", "") + "');", true); } }
protected void gvDetails_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { string FromDate = txtCollectionDate.Text; //============ Fill Data to dropdown of Gridview=================== DropDownList ddList = (DropDownList)e.Row.FindControl("drpInstrument"); DataTable dt = new DataTable(); List <string> ilist = new List <string>(); List <string> litem = new List <string>(); string query; //==========Instrument if (newdt == null) { query = "SELECT [Instrument_Code],[Instrument_Description] FROM AX.ACXINSTRUMENTMASTER WHERE DATAAREAID ='" + Session["DATAAREAID"].ToString() + "' AND Blocked = 0 ORDER BY [Instrument_Description]"; newdt = baseObj.GetData_New(query, CommandType.Text, ilist, litem); } ddList.DataSource = newdt; ddList.DataTextField = "Instrument_Description"; ddList.DataValueField = "Instrument_Code"; ddList.DataBind(); ddList.Items.Insert(0, new ListItem("--Select--", "0")); //==========Ref Doc Number Label RefDocNo = (Label)e.Row.FindControl("lblRefDocNo"); Label RefDate = (Label)e.Row.FindControl("lblRefDocDate"); Label PendingAmount = (Label)e.Row.FindControl("lblPendingAmount"); HiddenField customercode = (HiddenField)e.Row.FindControl("HiddenField2"); DropDownList ddRefNo = (DropDownList)e.Row.FindControl("drpRefDocument"); dt = new DataTable(); } }
private void ShowRecords(string PurchReceiptNumber) { DataTable dtLine = null; try { List <string> ilist = new List <string>(); List <string> litem = new List <string>(); ilist.Add("@PURCH_RECIEPTNO"); litem.Add(PurchReceiptNumber); ilist.Add("@SITEID"); litem.Add(Session["SiteCode"].ToString()); ilist.Add("@DATAAREAID"); litem.Add(Session["DATAAREAID"].ToString()); dtLine = obj.GetData_New("usp_Get_PurchaseReversalLine", CommandType.StoredProcedure, ilist, litem); Session[SessionGrid] = dtLine; if (dtLine.Rows.Count > 0) { //ViewState["dtgvDetails"] = dtLine; gvDetails.DataSource = dtLine; gvDetails.DataBind(); txtreversalVal.Text = Convert.ToDecimal(dtLine.Compute("SUM(RAMOUNT)", "")).ToString("0.0000"); gvDetails.Visible = true; } else { gvDetails.DataSource = null; gvDetails.DataBind(); LblMessage.Text = "No Line Items Exist"; // BtnUpdateHeader.Visible = false; } } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } }
//private void ExportToExcelNew() //{ // Response.Clear(); // Response.Buffer = true; // Response.ClearContent(); // Response.ClearHeaders(); // Response.Charset = ""; // string FileName = "OpenSalesOrder" + DateTime.Now + ".xls"; // StringWriter strwritter = new StringWriter(); // HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter); // Response.Cache.SetCacheability(HttpCacheability.NoCache); // Response.ContentType = "application/vnd.ms-excel"; // Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName); // gvDetails.GridLines = GridLines.Both; // gvDetails.HeaderStyle.Font.Bold = true; // gvDetails.RenderControl(htmltextwrtter); // { // Response.Write("<table><tr><td><b>From Date: " + txtFromDate.Text + "</b></td><td></td> <td><b>To Date: " + txtToDate.Text + "</b></td></tr></table>"); // } // Response.Write(strwritter.ToString()); // Response.End(); //} private void ExportToExcelNew() { bool b = ValidateSearch(); if (b == true) { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); try { string query = "ACX_USP_OpenSaleOrder"; List <string> ilist = new List <string>(); List <string> item = new List <string>(); DataTable dt = new DataTable(); if (ddlSiteId.SelectedItem.Text == "") { ilist.Add("@Site_Code"); item.Add(Session["SiteCode"].ToString()); } else { ilist.Add("@Site_Code"); item.Add(ddlSiteId.SelectedItem.Value.ToString()); } ilist.Add("@DATAAREAID"); item.Add(Session["DATAAREAID"].ToString()); ilist.Add("@StartDate"); item.Add(txtFromDate.Text); ilist.Add("@EndDate"); item.Add(txtToDate.Text); dt = obj.GetData_New(query, CommandType.StoredProcedure, ilist, item); //string FromDate = txtFromDate.Text; //string ToDate = txtToDate.Text; if (dt.Rows.Count > 0) { GridView gv = new GridView(); gv.DataSource = dt; gv.DataBind(); string FileName = "OpenSalesOrder" + DateTime.Now + ".xls"; StringWriter strwritter = new StringWriter(); HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter); Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName); LblMessage.Text = "Total Records : " + dt.Rows.Count.ToString(); gv.GridLines = GridLines.Both; gv.HeaderStyle.Font.Bold = true; gv.RenderControl(htmltextwrtter); { Response.Write("<table><tr><td><b>From Date: " + txtFromDate.Text + "</b></td><td></td> <td><b>To Date: " + txtToDate.Text + "</b></td></tr></table>"); } Response.Write(strwritter.ToString()); Response.End(); } else { LblMessage.Text = string.Empty; //this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('No Data Exits Between This Date Range !');", true); LblMessage.Text = "No Data Exits Between This Date Range ! !"; LblMessage.Visible = true; uppanel.Update(); } } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } } }
private void ShowReportSummary() { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); DataTable dtSetData = null; try { //string query = "Select Top 1 NAME from ax.inventsite where SITEID='" + Session["SiteCode"].ToString() + "'"; // object obj1 = obj.GetScalarValue(query); // string sqlstr = @"Select IH.INVOIC_DATE,Case when IH.TranType = 1 then 'SV' when IH.TranType = 2 then 'PS' end as TransType, // IH.INVOICE_NO,C.Customer_Name,IH.Customer_Code, CASE IH.TRANTYPE WHEN 1 THEN IL.BOX WHEN 2 THEN -IL.BOX END BOX, // CASE IH.TRANTYPE WHEN 1 THEN IL.LTR WHEN 2 THEN -IL.LTR END LTR,CASE IH.TRANTYPE WHEN 1 THEN IL.LINEAMOUNT WHEN 2 THEN -IL.LINEAMOUNT END LINEAMOUNT, // CASE IH.TRANTYPE WHEN 1 THEN IL.DISC_AMOUNT WHEN 2 THEN -IL.DISC_AMOUNT END DISC_AMOUNT, // CASE IH.TRANTYPE WHEN 1 THEN IL.TAX_AMOUNT WHEN 2 THEN -IL.TAX_AMOUNT END TAX_AMOUNT, // CASE IH.TRANTYPE WHEN 1 THEN IL.ADDTAX_AMOUNT WHEN 2 THEN -IL.ADDTAX_AMOUNT END ADDTAX_AMOUNT, // '' as Surcharge , CASE IH.TRANTYPE WHEN 1 THEN IL.AMOUNT WHEN 2 THEN -IL.AMOUNT END AMOUNT , '' as FOCAMOUNT , '' as SchemeAmount,* // from [ax].[ACXSALEINVOICEHEADER] IH // Left Join [ax].[ACXSALEINVOICELINE] IL on IH.SITEID = IL.SITEID and IL.INVOICE_NO = IH.INVOICE_NO // left join [ax].[ACXCUSTMASTER] C on IH.Customer_Code = C.CUSTOMER_CODE // and IL.DATAAREAID = IH.DATAAREAID // where IH.INVOIC_DATE >= " + // " '" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "' and IH.INVOIC_DATE <= '" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "' "; // if (ddlSiteId.SelectedIndex != -1) // { // if (ddlSiteId.SelectedItem.Text != "Select...") // { // sqlstr += "and IH.SITEID = '" + ddlSiteId.SelectedItem.Value + "' "; // } // } // sqlstr += " Order by IH.INVOIC_DATE "; dtSetData = new DataTable(); string query = "ACX_SALEREGISTER_DATEWISE"; List <string> ilist = new List <string>(); List <string> item = new List <string>(); string StateCode, SiteCode; SiteCode = ""; if (ddlSiteId.SelectedIndex >= 0) { if (ddlSiteId.SelectedItem.Text != "All...") { SiteCode = ddlSiteId.SelectedItem.Value; } } ilist.Add("@SITEID"); item.Add(SiteCode); StateCode = ""; if (ddlState.SelectedIndex > 0) { StateCode = ddlState.SelectedItem.Value; } ilist.Add("@STATE"); item.Add(StateCode); ilist.Add("@FROMDATE"); item.Add(txtFromDate.Text); ilist.Add("@TODATE"); item.Add(txtToDate.Text); ilist.Add("@BUCODE"); if (DDLBusinessUnit.SelectedIndex >= 1) { item.Add(DDLBusinessUnit.SelectedItem.Value.ToString()); } else { item.Add(""); } dtSetData = obj.GetData_New(query, CommandType.StoredProcedure, ilist, item); //dtSetData = obj.GetData(sqlstr); LoadDataInReportViewerDetail(dtSetData); } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } }
protected void Button1_Click(object sender, EventArgs e) { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); DataTable dt = new DataTable(); if (ddlProductGroup.SelectedIndex != 0) { LblMessage.Text = ""; try { string query = "ACX_USP_SaleRegisterMasterProductReport"; List <string> ilist = new List <string>(); List <string> item = new List <string>(); ilist.Add("@State_Code"); item.Add(ddlState.SelectedValue); ilist.Add("@Todate"); item.Add(txtToDate.Text); ilist.Add("@Fromdate"); item.Add(txtFromDate.Text); ilist.Add("@Site_Code"); item.Add(ddlSiteId.SelectedValue); ilist.Add("@ItemGroup"); item.Add(ddlProductGroup.SelectedValue); dt = obj.GetData_New(query, CommandType.StoredProcedure, ilist, item); if (dt.Rows.Count > 0) { //=================Create Excel========== string attachment = "attachment; filename=MasterProductSales.xls"; Response.ClearContent(); Response.AddHeader("content-disposition", attachment); Response.ContentType = "application/vnd.ms-excel"; string tab = ""; foreach (DataColumn dc in dt.Columns) { Response.Write(tab + dc.ColumnName); tab = "\t"; } Response.Write("\n"); int i; foreach (DataRow dr in dt.Rows) { tab = ""; for (i = 0; i < dt.Columns.Count; i++) { Response.Write(tab + dr[i].ToString()); tab = "\t"; } Response.Write("\n"); } Response.Flush(); Response.SuppressContent = true; } } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } } else { LblMessage.Text = "Select Product Group"; } }
protected void BtnShowReport_Click(object sender, EventArgs e) { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); DataTable dt = new DataTable(); bool b = ValidateInput(); if (b == true) { LblMessage.Text = ""; try { string query = "ACX_USP_SaleRegisterProductGroupReport"; List <string> ilist = new List <string>(); List <string> item = new List <string>(); ilist.Add("@State_Code"); item.Add(ddlState.SelectedValue); ilist.Add("@Todate"); item.Add(txtToDate.Text); ilist.Add("@Fromdate"); item.Add(txtFromDate.Text); if (ddlSiteId.SelectedIndex < 0) { if (Convert.ToString(Session["LOGINTYPE"]) == "3") { string siteid = ""; DataTable dt1 = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE); foreach (DataRow row in dt1.Rows) { if (siteid == "") { siteid = Convert.ToString(row["DISTRIBUTOR"]); } else { siteid += "," + row["DISTRIBUTOR"]; } } } else { //cmd.Parameters.AddWithValue("@SiteId", ""); ilist.Add("@Site_Code"); item.Add(""); } } else if (ddlSiteId.SelectedIndex > 0) { //cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value); ilist.Add("@Site_Code"); item.Add(ddlSiteId.SelectedItem.Value); } else if (ddlSiteId.SelectedItem.Text != "All...") { //cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value); ilist.Add("@Site_Code"); item.Add(ddlSiteId.SelectedItem.Value); } else { if (Convert.ToString(Session["LOGINTYPE"]) == "3") { string siteid = ""; DataTable dt1 = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE); foreach (DataRow row in dt1.Rows) { if (siteid == "") { siteid = Convert.ToString(row["DISTRIBUTOR"]); } else { siteid += "," + row["DISTRIBUTOR"]; } } //cmd.Parameters.AddWithValue("@SiteId", siteid); ilist.Add("@Site_Code"); item.Add(siteid); } else { //cmd.Parameters.AddWithValue("@SiteId", ""); ilist.Add("@Site_Code"); item.Add(""); } } //if (ddlSiteId.SelectedValue == "All...") //{ // ilist.Add("@Site_Code"); item.Add(""); //} //else //{ // ilist.Add("@Site_Code"); item.Add(ddlSiteId.SelectedValue); //} ilist.Add("@ItemGroup"); item.Add(ddlProductGroup.SelectedValue); if (ddlPSR.SelectedItem.Text != "All...") { ilist.Add("@PSR"); item.Add(ddlPSR.SelectedValue); } dt = obj.GetData_New(query, CommandType.StoredProcedure, ilist, item); if (dt.Rows.Count > 0) { //=================Create Excel========== string attachment = "attachment; filename=SaleRegisterSalesProductWise.xls"; Response.ClearContent(); Response.AddHeader("content-disposition", attachment); Response.ContentType = "application/vnd.ms-excel"; string tab = ""; foreach (DataColumn dc in dt.Columns) { Response.Write(tab + dc.ColumnName); tab = "\t"; } Response.Write("\n"); int i; foreach (DataRow dr in dt.Rows) { tab = ""; for (i = 0; i < dt.Columns.Count; i++) { Response.Write(tab + dr[i].ToString()); tab = "\t"; } Response.Write("\n"); } Response.Flush(); Response.SuppressContent = true; } } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } } }
protected void ExportDataToExcel() { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); DataTable dt = new DataTable(); SqlCommand cmd = new SqlCommand(); cmd.CommandTimeout = 0; bool b = ValidateInput(); if (b == true) { try { string FromDate = txtFromDate.Text; string ToDate = txtToDate.Text; string Site_Code = String.Empty; string State_Code = String.Empty; string DATAAREAID = String.Empty; string UserType = String.Empty; string UserCode = String.Empty; string query = "ACX_USP_ConsolidateSaleRegister"; List <string> ilist = new List <string>(); List <string> item = new List <string>(); ilist.Add("@Site_Code"); Site_Code = ""; if (ddlSiteId.SelectedIndex < 0) { if (Convert.ToString(Session["LOGINTYPE"]) == "3") { Site_Code = ""; DataTable dt1 = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE); foreach (DataRow row in dt1.Rows) { if (Site_Code == "") { Site_Code = Convert.ToString(row["DISTRIBUTOR"]); } else { Site_Code += "," + row["DISTRIBUTOR"]; } } } else { Site_Code = ""; } } else if (ddlSiteId.SelectedIndex > 0) { Site_Code = ddlSiteId.SelectedItem.Value;; } else if (ddlSiteId.SelectedItem.Text != "All...") { Site_Code = ddlSiteId.SelectedItem.Value; } else { if (Convert.ToString(Session["LOGINTYPE"]) == "3") { DataTable dt1 = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE); foreach (DataRow row in dt1.Rows) { if (Site_Code == "") { Site_Code = Convert.ToString(row["DISTRIBUTOR"]); } else { Site_Code += "," + row["DISTRIBUTOR"]; } } } else { Site_Code = ""; } } item.Add(Site_Code); ilist.Add("@State_Code"); if (ddlState.SelectedIndex != -1) { if (ddlState.SelectedItem.Text != "All...") { State_Code = ddlState.SelectedItem.Value; item.Add(State_Code); } else { item.Add(""); } } else { item.Add("0"); } ilist.Add("@DATAAREAID"); item.Add(Session["DATAAREAID"].ToString()); ilist.Add("@StartDate"); item.Add(txtFromDate.Text); ilist.Add("@EndDate"); item.Add(txtToDate.Text); ilist.Add("@UserType"); item.Add(Session["LOGINTYPE"].ToString()); ilist.Add("@UserCode"); item.Add(Session["USERID"].ToString()); ilist.Add("@BUCODE"); if (DDLBusinessUnit.SelectedIndex >= 1) { item.Add(DDLBusinessUnit.SelectedItem.Value.ToString()); } else { item.Add(""); } dt = obj.GetData_New(query, CommandType.StoredProcedure, ilist, item); if (dt.Rows.Count > 0) { string attachment = "attachment; filename=" + "ConsolidatedSaleRegister" + ".xls"; Response.ClearContent(); Response.AddHeader("content-disposition", attachment); Response.ContentType = "application/vnd.ms-excel"; string tab = ""; foreach (DataColumn dc in dt.Columns) { Response.Write(tab + dc.ColumnName); tab = "\t"; } Response.Write("\n"); int i; foreach (DataRow dr in dt.Rows) { tab = ""; for (i = 0; i < dt.Columns.Count; i++) { Response.Write(tab + dr[i].ToString()); tab = "\t"; } Response.Write("\n"); } Response.End(); } } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } } }
protected void BtnShowReport_Click(object sender, EventArgs e) { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); DataTable dt = new DataTable(); bool b = ValidateInput(); if (b == true) { try { string FromDate = txtFromDate.Text; string ToDate = txtToDate.Text; string query = "ACX_USP_SaleRegisterReport"; List <string> ilist = new List <string>(); List <string> item = new List <string>(); ilist.Add("@Site_Code"); if (ddlSiteId.SelectedIndex != -1) { if (ddlSiteId.SelectedItem.Text != "Select...") { item.Add(ddlSiteId.SelectedItem.Value); } else { item.Add("0"); } } else { item.Add("0"); } ilist.Add("@State_Code"); if (ddlState.SelectedIndex != -1) { if (ddlState.SelectedItem.Text != "All...") { item.Add(ddlState.SelectedItem.Value); } else { item.Add("0"); } } else { item.Add("0"); } ilist.Add("@DATAAREAID"); item.Add(Session["DATAAREAID"].ToString()); ilist.Add("@StartDate"); item.Add(txtFromDate.Text); ilist.Add("@EndDate"); item.Add(txtToDate.Text); dt = obj.GetData_New(query, CommandType.StoredProcedure, ilist, item); if (dt.Rows.Count > 0) { LoadDataInReportViewerDetail(dt); ReportViewer1.Visible = true; } else { ReportViewer1.Visible = false; } } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } } }
protected void gvDetails_RowDataBound(object sender, GridViewRowEventArgs e) { try { if (e.Row.RowType == DataControlRowType.DataRow) { //============ Fill Data to dropdown of Gridview=================== DropDownList ddList = (DropDownList)e.Row.FindControl("drpInstrument"); DataTable dt = new DataTable(); List <string> ilist = new List <string>(); List <string> litem = new List <string>(); string query; //==========Instrument if (newdt == null) { query = "Select [Instrument_Code],[Instrument_Description] from ax.ACXINSTRUMENTMASTER where DATAAREAID ='" + Session["DATAAREAID"].ToString() + "' and Blocked = 0 order by [Instrument_Description]"; newdt = baseObj.GetData_New(query, CommandType.Text, ilist, litem); } ddList.DataSource = newdt; ddList.DataTextField = "Instrument_Description"; ddList.DataValueField = "Instrument_Code"; ddList.DataBind(); ddList.Items.Insert(0, new ListItem("--Select--", "0")); //==========Ref Doc Number HiddenField customercode = (HiddenField)e.Row.FindControl("HiddenField2"); DropDownList ddRefNo = (DropDownList)e.Row.FindControl("drpRefDocument"); dt = new DataTable(); //query = "select distinct ax.ACXCUSTTRANS.[Document_No] from ax.ACXCUSTTRANS INNER JOIN ax.ACXCUSTTRANS ACXCUSTTRANS_1 ON ax.ACXCUSTTRANS.Document_No <> ACXCUSTTRANS_1.RefNo_DocumentNo " + // " where ax.ACXCUSTTRANS.RemainingAmount<>0 and ax.ACXCUSTTRANS.DocumentType<>2 and ax.ACXCUSTTRANS.[Customer_Code]='" + customercode.Value + "' and ax.ACXCUSTTRANS.[SiteCode]='" + Session["SiteCode"].ToString() + "' and ax.ACXCUSTTRANS.[DATAAREAID]='" + Session["DATAAREAID"].ToString() + "' " + // " order by ax.ACXCUSTTRANS.[Document_No] desc "; //query = " select ax.ACXCUSTTRANS.[Document_No] from ax.ACXCUSTTRANS where ax.ACXCUSTTRANS.RemainingAmount>0 And AMOUNT>=REMAININGAMOUNT " + // // " ax.ACXCUSTTRANS.RefNo_DocumentNo is null and ax.ACXCUSTTRANS.[Document_No] not in (select ax.ACXCUSTTRANS.RefNo_DocumentNo from ax.ACXCUSTTRANS where ax.ACXCUSTTRANS.RefNo_DocumentNo is not null) " + // " and ax.ACXCUSTTRANS.[Customer_Code]='" + customercode.Value + "' and ax.ACXCUSTTRANS.[SiteCode]='" + Session["SiteCode"].ToString() + "' and ax.ACXCUSTTRANS.[DATAAREAID]='" + Session["DATAAREAID"].ToString() + "' " + // " and ax.ACXCUSTTRANS.[Document_No] not in (select SO_NO from ax.ACXSALEINVOICEheader where trantype=2 and ax.ACXCUSTTRANS.[Document_No]=ax.ACXSALEINVOICEheader.SO_NO and ax.ACXCUSTTRANS.[SiteCode]=ax.ACXSALEINVOICEheader.SiteID)"+ // " order by ax.ACXCUSTTRANS.[Document_No] desc "; //flag = 0; if (drpCustomerName.SelectedItem.Value != "--Select--") { query = "EXEC ACX_GETCOLLECTIONENTRYREPORT'" + Session["SiteCode"].ToString() + "','" + drpCustomerGroup.SelectedValue + "','" + drpCustomerName.SelectedItem.Value + "' "; dt = baseObj.GetData_New(query, CommandType.Text, ilist, litem); ddRefNo.DataSource = dt; ddRefNo.DataTextField = "Document_No"; ddRefNo.DataValueField = "Document_No"; ddRefNo.DataBind(); ddRefNo.Items.Insert(0, new ListItem("--Select--", "0")); Session["AllCusData"] = null; } else { if (Session["AllCusData"] == null) { query = "EXEC ACX_GETCOLLECTIONENTRYREPORT'" + Session["SiteCode"].ToString() + "','" + drpCustomerGroup.SelectedValue + "',''"; Completedata = baseObj.GetData_New(query, CommandType.Text, ilist, litem); Session["AllCusData"] = Completedata; } else { Completedata = (DataTable)Session["AllCusData"]; } DataTable _newDataTable = new DataTable(); var rows = Completedata.AsEnumerable().Where (row => row.Field <string>("CUSTOMER_CODE").ToLower() == customercode.Value.ToLower()); if (rows.Any()) { _newDataTable = rows.CopyToDataTable <DataRow>(); } ddRefNo.DataSource = _newDataTable; ddRefNo.DataTextField = "Document_No"; ddRefNo.DataValueField = "Document_No"; ddRefNo.DataBind(); ddRefNo.Items.Insert(0, new ListItem("--Select--", "0")); } //else //{ // query = "EXEC ACX_GETCOLLECTIONENTRYREPORT'" + Session["SiteCode"].ToString() + "','" + drpCustomerGroup.SelectedValue + "','" + customercode.Value + "' "; // dt = baseObj.GetData_New(query, CommandType.Text, ilist, litem); // ddRefNo.DataSource = dt; // ddRefNo.DataTextField = "Document_No"; // ddRefNo.DataValueField = "Document_No"; // ddRefNo.DataBind(); // ddRefNo.Items.Insert(0, new ListItem("--Select--", "0")); //} } } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); } }
protected void ExportDataToExcel() { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); DataTable dt = new DataTable(); bool b = ValidateInput(); if (b == true) { try { string FromDate = txtFromDate.Text; string ToDate = txtToDate.Text; string query = "ACX_USP_PurchaseRegisterREPORT"; List <string> ilist = new List <string>(); List <string> item = new List <string>(); //if (ddlSiteId.SelectedIndex != -1) //{ // if (ddlSiteId.SelectedItem.Text != "All...") // { // item.Add(ddlSiteId.SelectedItem.Value); // } // else // { // item.Add("0"); // } //} //else //{ // item.Add("0"); //} //SITEID ilist.Add("@Site_Code"); if (ddlSiteId.SelectedIndex < 0) { if (Convert.ToString(Session["LOGINTYPE"]) == "3") { string Site_Code = ""; dt = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE); foreach (DataRow row in dt.Rows) { if (Site_Code == "") { Site_Code = Convert.ToString(row["DISTRIBUTOR"]); } else { Site_Code += "," + row["DISTRIBUTOR"]; } } } else { item.Add(""); } } else if (ddlSiteId.SelectedIndex > 0) { item.Add(ddlSiteId.SelectedItem.Value); } else if (ddlSiteId.SelectedItem.Text != "All...") { item.Add(ddlSiteId.SelectedItem.Value); } else { if (Convert.ToString(Session["LOGINTYPE"]) == "3") { string Site_Code = ""; dt = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE); foreach (DataRow row in dt.Rows) { if (Site_Code == "") { Site_Code = Convert.ToString(row["DISTRIBUTOR"]); } else { Site_Code += "," + row["DISTRIBUTOR"]; } } item.Add(Site_Code); } else { item.Add(""); } } //STATE ilist.Add("@StateCode"); if (ddlState.SelectedIndex != -1) { if (ddlState.SelectedItem.Text != "--Select--") { item.Add(ddlState.SelectedItem.Value); } else { item.Add("0"); } } ilist.Add("@DATAAREAID"); item.Add(Session["DATAAREAID"].ToString()); ilist.Add("@StartDate"); item.Add(txtFromDate.Text); ilist.Add("@EndDate"); item.Add(txtToDate.Text); ilist.Add("@UserType"); item.Add(Session["LOGINTYPE"].ToString()); ilist.Add("@UserCode"); item.Add(Session["USERID"].ToString()); dt = obj.GetData_New(query, CommandType.StoredProcedure, ilist, item); if (dt.Rows.Count > 0) { //=================Create Excel========== string attachment = "attachment; filename=ConsolidatedPurchaseRegister.xls"; Response.ClearContent(); Response.AddHeader("content-disposition", attachment); Response.ContentType = "application/vnd.ms-excel"; string tab = ""; foreach (DataColumn dc in dt.Columns) { Response.Write(tab + dc.ColumnName); tab = "\t"; } Response.Write("\n"); int i; foreach (DataRow dr in dt.Rows) { tab = ""; for (i = 0; i < dt.Columns.Count; i++) { Response.Write(tab + dr[i].ToString()); tab = "\t"; } Response.Write("\n"); } Response.Flush(); Response.SuppressContent = true; } } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } } }
protected void BtnShowReport_Click(object sender, EventArgs e) { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); DataTable dt = new DataTable(); bool b = ValidateInput(); if (b == true) { try { string FromDate = txtFromDate.Text; string ToDate = txtToDate.Text; string query = "ACX_USP_PurchaseRegisterREPORT"; List <string> ilist = new List <string>(); List <string> item = new List <string>(); ilist.Add("@Site_Code"); //if (ddlSiteId.SelectedIndex != -1) //{ // if (ddlSiteId.SelectedItem.Text != "All...") // { // item.Add(ddlSiteId.SelectedItem.Value); // } // else // { // item.Add("0"); // } //} //else //{ // item.Add("0"); //} if (ddlSiteId.SelectedIndex < 0) { if (Convert.ToString(Session["LOGINTYPE"]) == "3") { string Site_Code = ""; dt = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE); foreach (DataRow row in dt.Rows) { if (Site_Code == "") { Site_Code = Convert.ToString(row["DISTRIBUTOR"]); } else { Site_Code += "," + row["DISTRIBUTOR"]; } } } else { item.Add(""); } } else if (ddlSiteId.SelectedIndex > 0) { string Site_Code = ddlSiteId.SelectedItem.Value; item.Add(Site_Code); } else if (ddlSiteId.SelectedItem.Text != "All...") { item.Add(ddlSiteId.SelectedItem.Value); } else { if (Convert.ToString(Session["LOGINTYPE"]) == "3") { string Site_Code = ""; dt = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE); foreach (DataRow row in dt.Rows) { if (Site_Code == "") { Site_Code = Convert.ToString(row["DISTRIBUTOR"]); } else { Site_Code += "," + row["DISTRIBUTOR"]; } } item.Add(Site_Code); } else { item.Add(""); } } //state ilist.Add("@StateCode"); if (ddlState.SelectedIndex != -1) { if (ddlState.SelectedItem.Text != "--Select--") { item.Add(ddlState.SelectedItem.Value); } else { item.Add("0"); } } ilist.Add("@DATAAREAID"); item.Add(Session["DATAAREAID"].ToString()); ilist.Add("@StartDate"); item.Add(txtFromDate.Text); ilist.Add("@EndDate"); item.Add(txtToDate.Text); ilist.Add("@UserType"); item.Add(Session["LOGINTYPE"].ToString()); ilist.Add("@UserCode"); item.Add(Session["USERID"].ToString()); dt = obj.GetData_New(query, CommandType.StoredProcedure, ilist, item); if (dt.Rows.Count > 0) { LoadDataInReportViewerDetail(dt); } } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } } }
private void ShowReportDetail() { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); DataTable dt = new DataTable(); try { //string query = "Select Top 1 NAME from ax.inventsite where SITEID='" + Session["SiteCode"].ToString() + "'"; //object obj1 = obj.GetScalarValue(query); string FromDate = txtFromDate.Text; string ToDate = txtToDate.Text; string Claim_Type = string.Empty; string SITEID = string.Empty; string Claim_Category = string.Empty; string CLAIM_SubCategory = string.Empty; string BUCODE = string.Empty; string query = "[dbo].[ACX_USP_EXPENSEDETAIL_TI_Version]"; //string query = "ACX_USP_EXPENSEDETAIL"; List <string> ilist = new List <string>(); List <string> item = new List <string>(); ilist.Add("@FromDate"); item.Add(txtFromDate.Text); ilist.Add("@ToDate"); item.Add(txtToDate.Text); //string sqlstr = "exec Acx_usp_ExpenseDetail '"+ Convert.ToDateTime(txtFromDate.Text) + "','" + Convert.ToDateTime(txtToDate.Text) + "' "; //string sqlstr = @"Select I.StateCode as State,I.Siteid as DistributorCode // ,I.Name DistributorName // ,Case when Claim_Type = '0' then 'Sale' else 'Purchase' end as ClaimType, // Document_Code as ClaimDocNo,DOCUMENT_DATE as ClaimDocDate,from_Date,To_Date, // Case when OBJECT =0 then 'PSR' when OBJECT =1 then 'SITE' when OBJECT =2 then 'Customer Group' end as Object, // Object_Code, // Object_Name1 = (Select Case when OBJECT = 0 then // (select Top 1 PSR_Name from [ax].[ACXPSRMaster] P where PSR_Code = Object_Code) // when OBJECT = 1 then // (select Top 1 Name from [ax].[INVENTSITE] where SiteID = Object_Code) // when OBJECT = 2 then // (Select Top 1 CUSTOMER_NAME from [ax].[ACXCUSTMASTER] where Customer_Code= Object_Code) // end ), // Object_SubCode,Target_GROUP, // Object_SubName = (Select Case when OBJECT = 2 then // (select Top 1 CUSTGROUP_NAME from [ax].[ACXCUSTGROUPMASTER] where CustGroup_Code = Object_SubCode) else '' end), // Target_Code,Target_Description,CM.Target,ACHIVEMENT,ACTUAL_INCENTIVE,TARGET_CODE,tl.CAlCULATIONON,Target_INCENTIVE, // TC.Name as Cat,TS.Name as Subcat,CM.CALCULATION_PATTERN from [ax].[ACXCLAIMMASTER] CM // left join [ax].[ACXTARGETCATEGORY] TC on CM.Claim_Category = TC.CATEGORY // left join [ax].[ACXTARGETSUBCATEGORY] TS on CM.CLAIM_SUBCATEGORY = TS.Subcategory // left Join [ax].[ACXtargetheader] th on CM.Target_Code = th.targetcode // left join [ax].[ACXTARGETLINE] tl on th.TargetCode = tl.TargetCode and th.DataAreaId=tl.DataAreaId // AND CM.OBJECT=TL.TARGETOBJECT AND CM.OBJECT_SUBCODE=TL.TARGETSUBOBJECT AND CM.OBJECT_CODE=TL.OBJECTCODE // AND CM.FROM_DATE=TL.STARTDATE AND CM.TO_DATE=TL.ENDDATE AND CM.TARGET=TL.TARGET // left join [ax].[INVENTSITE] I on CM.SITE_CODE = I.Siteid where" + // " CM.from_Date >='" + Convert.ToDateTime(txtFromDate.Text) + "' and CM.TO_DATE <= '" + Convert.ToDateTime(txtToDate.Text) + "' "; ilist.Add("@Claim_Type"); if (ddlClaimType.SelectedItem.Text != "Select...") { //item.Add(ddlClaimType.SelectedItem.Value); if (ddlClaimType.SelectedItem.Text == "Sale") { Claim_Type = "0"; item.Add(Claim_Type); } else if (ddlClaimType.SelectedItem.Text == "Purchase") { Claim_Type = "1"; item.Add(Claim_Type); } } else { item.Add(""); } ilist.Add("@SITEID"); if (ddlSiteId.SelectedIndex != -1) { if (ddlSiteId.SelectedItem.Text != "Select...") { // item.Add(ddlSiteId.SelectedItem.Value); SITEID = ddlSiteId.SelectedItem.Value; item.Add(SITEID); } else { item.Add(""); } } else { item.Add(""); } ilist.Add("@Claim_Category"); if (ddlClaimCat.SelectedItem.Text != "Select..." && ddlClaimCat.SelectedItem.Text != "") { Claim_Category = ddlClaimCat.SelectedItem.Value; item.Add(Claim_Category); // item.Add(ddlClaimCat.SelectedItem.Value); } else { item.Add(""); } ilist.Add("@CLAIM_SubCategory"); if (ddlClaimSubCat.SelectedIndex != -1) { if (ddlClaimSubCat.SelectedItem.Text != "Select..." && ddlClaimSubCat.SelectedItem.Text != "") { CLAIM_SubCategory = ddlClaimSubCat.SelectedItem.Value; item.Add(CLAIM_SubCategory); // item.Add(ddlClaimSubCat.SelectedItem.Value); } else { item.Add(""); } } else { item.Add(""); } ilist.Add("@BUCode"); if (ddlBusinessUnit.SelectedIndex != -1) { if (ddlBusinessUnit.SelectedItem.Text != "Select..." && ddlBusinessUnit.SelectedItem.Text != "") { BUCODE = ddlBusinessUnit.SelectedItem.Value; item.Add(BUCODE); } else { item.Add(""); } } else { item.Add(""); } dt = obj.GetData_New(query, CommandType.StoredProcedure, ilist, item); if (dt.Rows.Count > 0) { LblMessage.Text = ""; LoadDataInReportViewerDetail(dt); ReportViewer1.Visible = true; } else { LblMessage.Text = "No data available for selected criteria, please try again."; ReportViewer1.Visible = false; } } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } }
protected void btnMultiPrint_Click(object sender, EventArgs e) { string multipleInvoice = string.Empty; string MulqueryHeader = "USP_ServiceInvoiceHeaderReport"; string MulqueryLine = "USP_ServiceInvoiceLineReport"; string MulqueryGST = "USP_SERVICEINVOICETAXTRAN"; List <string> ilist = new List <string>(); List <string> litem = new List <string>(); DataTable dtMulHeader, dtMulLine, dtMulGst; DataTable dtGSTCAL = null; dtMulHeader = new DataTable(); dtMulLine = new DataTable(); dtMulGst = new DataTable(); foreach (GridViewRow grv in GridView1.Rows) { CheckBox chklist1 = (CheckBox)grv.Cells[0].FindControl("chklist"); LinkButton lnkBtn = (LinkButton)grv.Cells[0].FindControl("lnkbtn"); string Siteid = ((HiddenField)grv.FindControl("hndSiteid")).Value.ToString(); if (chklist1.Checked) { ilist = new List <string>(); litem = new List <string>(); ilist.Add("@Invoice_No"); litem.Add(lnkBtn.Text); ilist.Add("@SiteID"); litem.Add(Siteid.ToString()); dtHeader = obj.GetData_New(MulqueryHeader, CommandType.StoredProcedure, ilist, litem); dtMulHeader.Merge(dtHeader); dtLinetest = obj.GetData_New(MulqueryLine, CommandType.StoredProcedure, ilist, litem); dtMulLine.Merge(dtLinetest); dtGSTCAL = obj.GetData_New(MulqueryGST, CommandType.StoredProcedure, ilist, litem); dtMulGst.Merge(dtGSTCAL); } } string[] strSavePDFID = new string[dtMulHeader.Rows.Count + 1]; //for merging a pdf into single one,I want extra pdf and for this i have called SaveInvoicePDf funtion //for single invoice PDF i have created 2 pdf and murge into one SaveInvoicePDf(dtMulHeader, dtMulLine, dtMulGst, 0, 0, strSavePDFID); //SaveInvoicePDf(dtMainHeader1, dtLine1, 0, 0, strSavePDFID); // for (int i = 0; i < dtMulHeader.Rows.Count; i++) { SaveInvoicePDf(dtMulHeader, dtMulLine, dtMulGst, i, i + 1, strSavePDFID); } if (strSavePDFID.Length > 0) { string des = Server.MapPath("Downloads\\" + strSavePDFID[0] + ".pdf"); string[] source = new string[strSavePDFID.Length - 1]; for (int i = 0; i < strSavePDFID.Length - 1; i++) { source[i] = Server.MapPath("Downloads\\" + strSavePDFID[i + 1] + ".pdf"); } MergeFiles(des, source); string FilePath = Server.MapPath("Downloads\\" + strSavePDFID[0] + ".pdf"); WebClient User = new WebClient(); Byte[] FileBuffer = User.DownloadData(FilePath); //Delete the files from system.......... if (strSavePDFID.Length > 0) { for (int i = 0; i < strSavePDFID.Length; i++) { string path = Server.MapPath("Downloads\\" + strSavePDFID[i] + ".pdf"); FileInfo file = new FileInfo(path); if (file.Exists)//check file exsit or not { file.Delete(); } } } if (FileBuffer != null) { Response.AddHeader("Content-Disposition", "attachment;filename=" + strSavePDFID[0] + ".pdf"); Response.BinaryWrite(FileBuffer); Response.Flush(); Response.End(); } } // murgebytges(); }