private void ShowReportByFilter() { CreamBell_DMS_WebApps.App_Code.Global objGlobal = new CreamBell_DMS_WebApps.App_Code.Global(); SqlConnection conn = null; SqlCommand cmd = null; SqlCommand cmd1 = null; DataTable dtDataByfilter = null; string query = string.Empty; try { // conn = new SqlConnection(objGlobal.GetConnectionString()); // conn.Open(); cmd = new SqlCommand(); // cmd.Connection = conn; cmd.CommandTimeout = 3600; cmd.CommandType = CommandType.StoredProcedure; query = "[ax].[ACX_VRS_MonthlySummerySheet]"; cmd.CommandText = query; DateTime stDate = Convert.ToDateTime(txtFromDate.Text); DateTime firstDayOfMonth = new DateTime(stDate.Year, stDate.Month, 1); DateTime lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1); cmd.Parameters.AddWithValue("@StartDate", firstDayOfMonth); cmd.Parameters.AddWithValue("@EndDate", lastDayOfMonth); if (Convert.ToString(Session["LOGINTYPE"]) == "3") { cmd.Parameters.AddWithValue("@SiteId", ucRoleFilters.GetCommaSepartedSiteId()); } else { if (ddlSiteId.SelectedIndex >= 1) { cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value); } else { cmd.Parameters.AddWithValue("@SiteId", "0"); } } cmd.Parameters.AddWithValue("@VRSCode", ddlVRS.SelectedValue.ToString()); dtDataByfilter = new DataTable(); cmd.Connection = objGlobal.GetConnection(); dtDataByfilter.Load(cmd.ExecuteReader()); //==============Claim Data=================== DataTable dtClaim = new DataTable(); cmd1 = new SqlCommand(); //cmd1.Connection = conn; cmd1.CommandTimeout = 3600; cmd1.CommandType = CommandType.StoredProcedure; query = "[ax].[ACX_VRS_MonthlySummerySheet_CLAIM]"; cmd1.CommandText = query; cmd1.Parameters.AddWithValue("@StartDate", firstDayOfMonth); cmd1.Parameters.AddWithValue("@EndDate", lastDayOfMonth); if (Convert.ToString(Session["LOGINTYPE"]) == "3") { cmd1.Parameters.AddWithValue("@SiteId", ucRoleFilters.GetCommaSepartedSiteId()); } else { if (ddlSiteId.SelectedIndex >= 1) { cmd1.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value); } else { cmd1.Parameters.AddWithValue("@SiteId", "0"); } } if (ddlBusinessUnit.SelectedIndex >= 1) { cmd1.Parameters.AddWithValue("@BUCODE", ddlBusinessUnit.SelectedItem.Value.ToString()); } else { cmd1.Parameters.AddWithValue("@BUCODE", ""); } cmd1.Parameters.AddWithValue("@VRSCode", ddlVRS.SelectedValue.ToString()); dtClaim = new DataTable(); cmd1.Connection = objGlobal.GetConnection(); dtClaim.Load(cmd1.ExecuteReader()); LoadDataInReportViewer(dtDataByfilter, dtClaim); } catch (Exception ex) { this.LblMessage.Visible = true; this.LblMessage.Text = "► " + ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } finally { objGlobal.CloseSqlConnection(); } }
private void ShowData_ForExcel() { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); string FilterQuery = string.Empty; DataTable dtSetHeader = null; try { string query1 = "Select NAME from ax.inventsite where SITEID IN (" + ucRoleFilters.GetCommaSepartedSiteId() + ") "; dtSetHeader = new DataTable(); dtSetHeader = obj.GetData(query1); SqlConnection conn = null; SqlCommand cmd = null; DataTable dtDataByfilter = null; string query = string.Empty; //conn = new SqlConnection(obj.GetConnectionString()); //conn.Open(); cmd = new SqlCommand(); // cmd.Connection = conn; cmd.CommandTimeout = 3600; cmd.CommandType = CommandType.StoredProcedure; query = "ACX_PartyWiseSaleSummaryDiscount_ExcelOutput"; cmd.CommandText = query; DateTime now = Convert.ToDateTime(txtToDate.Text); now = now.AddMonths(1); cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd")); cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd")); string SiteList = ""; string StateList = ""; if (Convert.ToString(Session["LOGINTYPE"]) == "3") { SiteList = ucRoleFilters.GetCommaSepartedSiteId(); StateList = ucRoleFilters.GetCommaSepartedStateId(); } else { foreach (ListItem item in chkListSite1.Items) { if (item.Selected) { if (SiteList == "") { SiteList += "" + item.Value.ToString() + ""; } else { SiteList += "," + item.Value.ToString() + ""; } } } foreach (ListItem item in chkListState1.Items) { if (item.Selected) { if (StateList == "") { StateList += "" + item.Value.ToString() + ""; } else { StateList += "," + item.Value.ToString() + ""; } } } } if (SiteList.Length > 0) { cmd.Parameters.AddWithValue("@SiteId", SiteList); } else { cmd.Parameters.AddWithValue("@SiteId", ""); } if (StateList.Length > 0) { cmd.Parameters.AddWithValue("@STATECODE", StateList); } else { cmd.Parameters.AddWithValue("@STATECODE", ""); } string CustGroupList = ""; foreach (ListItem item in chkListCustomerGroup.Items) { if (item.Selected) { if (CustGroupList == "") { CustGroupList += "" + item.Value.ToString() + ""; } else { CustGroupList += "," + item.Value.ToString() + ""; } } } if (CustGroupList.Length > 0) { cmd.Parameters.AddWithValue("@CUSTGROUP", CustGroupList); } else { cmd.Parameters.AddWithValue("@CUSTGROUP", ""); } dtDataByfilter = new DataTable(); cmd.Connection = obj.GetConnection(); dtDataByfilter.Load(cmd.ExecuteReader()); DataTable dt = new DataTable(); #region product group string ProductGroupList = ""; foreach (ListItem item in chkProductGroup.Items) { if (item.Selected) { if (ProductGroupList == "") { ProductGroupList += "'" + item.Value.ToString() + "'"; } else { ProductGroupList += ",'" + item.Value.ToString() + "'"; } } } if (ProductGroupList.Length > 0) { dt = dtDataByfilter.Select("PRODUCT_GROUP IN (" + ProductGroupList + ")").CopyToDataTable(); } else { dt = dtDataByfilter; } #endregion //dt = dtDataByfilter; //=================Create Excel========== string attachment = "attachment; filename=PartyWiseSaleSummaryDiscount.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); } finally { obj.CloseSqlConnection(); } }
private void ShowReport() { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); string FilterQuery = string.Empty; DataTable dtSetHeader = null; DataTable dtSetData = null; DataTable dtTotalInvoiceNo = null; try { string query1 = "Select NAME from ax.inventsite where SITEID IN (" + ucRoleFilters.GetCommaSepartedSiteId() + ") "; dtSetHeader = new DataTable(); dtSetHeader = obj.GetData(query1); SqlConnection conn = null; SqlCommand cmd = null; DataTable dtDataByfilter = null; string query = string.Empty; //conn = new SqlConnection(obj.GetConnectionString()); //conn.Open(); cmd = new SqlCommand(); //cmd.Connection = conn; cmd.CommandTimeout = 0; cmd.CommandType = CommandType.StoredProcedure; // string qr = ""; query = "[ax].[ACX_PartyWiseSaleSummaryDiscount]"; cmd.CommandText = query; DateTime now = Convert.ToDateTime(txtToDate.Text); now = now.AddMonths(1); //DateTime lastDay = new DateTime(now.Year, now.Month, 1); cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd")); cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd")); string SiteList = ""; string StateList = ""; if (Convert.ToString(Session["LOGINTYPE"]) == "3") { SiteList = ucRoleFilters.GetCommaSepartedSiteId(); StateList = ucRoleFilters.GetCommaSepartedStateId(); } else { foreach (ListItem item in chkListSite1.Items) { if (item.Selected) { if (SiteList == "") { SiteList += "" + item.Value.ToString() + ""; } else { SiteList += "," + item.Value.ToString() + ""; } } } foreach (ListItem item in chkListState1.Items) { if (item.Selected) { if (StateList == "") { StateList += "" + item.Value.ToString() + ""; } else { StateList += "," + item.Value.ToString() + ""; } } } } if (SiteList.Length > 0) { cmd.Parameters.AddWithValue("@SiteId", SiteList); } else { cmd.Parameters.AddWithValue("@SiteId", ""); } if (StateList.Length > 0) { cmd.Parameters.AddWithValue("@STATECODE", StateList); } else { cmd.Parameters.AddWithValue("@STATECODE", ""); } string CustGroupList = ""; foreach (ListItem item in chkListCustomerGroup.Items) { if (item.Selected) { if (CustGroupList == "") { CustGroupList += "" + item.Value.ToString() + ""; } else { CustGroupList += "," + item.Value.ToString() + ""; } } } if (CustGroupList.Length > 0) { cmd.Parameters.AddWithValue("@CUSTGROUP", CustGroupList); } else { cmd.Parameters.AddWithValue("@CUSTGROUP", ""); } cmd.Connection = obj.GetConnection(); dtDataByfilter = new DataTable(); dtDataByfilter.Load(cmd.ExecuteReader()); string queryTotInv = " Select Count(Distinct INVOICE_NO) as InvoiceNo FROM ACX_SALESUMMARY_PARTY_ITEM_WISE SP " + " Inner Join [ax].[ACXCUSTMASTER] C on C.Customer_Code = SP.CUSTOMER_CODE and C.APPLICABLESCHEMEDISCOUNT = '2' " + " where SITEID IN (" + ucRoleFilters.GetCommaSepartedSiteId() + ") and INVOICE_DATE >=" + " '" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "' and INVOICE_DATE <='" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "' group by SP.CUSTOMER_NAME"; dtSetData = new DataTable(); #region product group string ProductGroupList = ""; foreach (ListItem item in chkProductGroup.Items) { if (item.Selected) { if (ProductGroupList == "") { ProductGroupList += "'" + item.Value.ToString() + "'"; } else { ProductGroupList += ",'" + item.Value.ToString() + "'"; } } } if (ProductGroupList.Length > 0) { dtSetData = dtDataByfilter.Select("PRODUCT_GROUP IN (" + ProductGroupList + ")").CopyToDataTable(); } else { dtSetData = dtDataByfilter; } #endregion dtTotalInvoiceNo = obj.GetData(queryTotInv); LoadDataInReportViewer(dtSetHeader, dtSetData, dtTotalInvoiceNo); } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } finally { obj.CloseSqlConnection(); } }
private void ShowReportByFilter() { CreamBell_DMS_WebApps.App_Code.Global objGlobal = new CreamBell_DMS_WebApps.App_Code.Global(); SqlConnection conn = null; SqlCommand cmd = null; DataTable dtDataByfilter = null; string query = string.Empty; try { conn = new SqlConnection(objGlobal.GetConnectionString()); //conn.Open(); cmd = new SqlCommand(); //cmd.Connection = conn; cmd.CommandTimeout = 0; cmd.CommandType = CommandType.StoredProcedure; query = "[ax].[ACX_MRPDiscountClaimReport]"; cmd.CommandText = query; //DateTime lastDay = new DateTime(now.Year, now.Month, 1); cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(txtFromDate.Text).ToString("dd-MMM-yyy")); cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text).ToString("dd-MMM-yyy")); string ListState = ""; string DistributorList = ""; if (Convert.ToString(Session["LOGINTYPE"]) == "3") { ListState = ucRoleFilters.GetCommaSepartedStateId(); DistributorList = ucRoleFilters.GetCommaSepartedSiteId(); } else { foreach (ListItem item in chkListState.Items) { if (item.Selected) { if (ListState == "") { ListState += "" + item.Value.ToString() + ""; } else { ListState += "," + item.Value.ToString() + ""; } } } foreach (ListItem item in chkListSite.Items) { if (item.Selected) { if (DistributorList == "") { DistributorList += "" + item.Value.ToString() + ""; } else { DistributorList += "," + item.Value.ToString() + ""; } } } } if (ListState.Length > 0) { cmd.Parameters.AddWithValue("@STATECODE", ListState); } else { cmd.Parameters.AddWithValue("@STATECODE", ""); } if (DistributorList.Length > 0) { cmd.Parameters.AddWithValue("@SiteId", DistributorList); } else { cmd.Parameters.AddWithValue("@SiteId", ""); } string CustGroupList = ""; foreach (ListItem item in chkListCustomerGroup.Items) { if (item.Selected) { if (CustGroupList == "") { CustGroupList += "" + item.Value.ToString() + ""; } else { CustGroupList += "," + item.Value.ToString() + ""; } } } if (CustGroupList.Length > 0) { cmd.Parameters.AddWithValue("@CUSTOMER_CODE", CustGroupList); } else { cmd.Parameters.AddWithValue("@CUSTOMER_CODE", ""); } string buunits = ""; foreach (ListItem items in Chkbu.Items) { if (items.Selected) { if (buunits == "") { buunits += "" + items.Value.ToString() + ""; } else { buunits += "," + items.Value.ToString() + ""; } } } if (buunits.Length > 0) { cmd.Parameters.AddWithValue("@BUCODE", buunits); } else { cmd.Parameters.AddWithValue("@BUCODE", ""); } dtDataByfilter = new DataTable(); cmd.Connection = objGlobal.GetConnection(); dtDataByfilter.Load(cmd.ExecuteReader()); LoadDataInReportViewer(dtDataByfilter); } catch (Exception ex) { this.LblMessage.Visible = true; this.LblMessage.Text = "► " + ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } finally { //if (conn != null) //{ // if (conn.State.ToString() == "Open") { conn.Close(); } //} objGlobal.CloseSqlConnection(); } }
private void ShowReportByFilter() { CreamBell_DMS_WebApps.App_Code.Global objGlobal = new CreamBell_DMS_WebApps.App_Code.Global(); SqlConnection conn = null; SqlCommand cmd = null; DataTable dtDataByfilter = null; string query = string.Empty; try { conn = new SqlConnection(objGlobal.GetConnectionString()); // conn.Open(); cmd = new SqlCommand(); // cmd.Connection = conn; cmd.CommandTimeout = 0; cmd.CommandType = CommandType.StoredProcedure; query = "[dbo].[ACX_USP_SALERANKINGREPORT]"; cmd.CommandText = query; DateTime now = Convert.ToDateTime(txtToDate.Text); now = now.AddMonths(1); //DateTime lastDay = new DateTime(now.Year, now.Month, 1); cmd.Parameters.AddWithValue("@STARTDATE", Convert.ToDateTime(txtFromDate.Text)); cmd.Parameters.AddWithValue("@ENDDATE", Convert.ToDateTime(txtToDate.Text)); cmd.Parameters.AddWithValue("@UserType", Convert.ToString(Session["LOGINTYPE"])); cmd.Parameters.AddWithValue("@UserCode", Convert.ToString(Session["USERID"])); if (drpCustGroup.SelectedItem.Text == "All...") { cmd.Parameters.AddWithValue("@CustGroup", ""); } else { cmd.Parameters.AddWithValue("@CustGroup", drpCustGroup.SelectedItem.Value.ToString()); } cmd.Parameters.AddWithValue("@State", ddlState.SelectedItem.Value.ToString()); //if (ddlSiteId.SelectedIndex >= 1) //{ // cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value); //} //else //{ // cmd.Parameters.AddWithValue("@SiteId", " "); //} if (ddlSiteId.SelectedIndex < 0) { if (Convert.ToString(Session["LOGINTYPE"]) == "3") { string siteid = ""; DataTable 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 (siteid == "") { siteid = Convert.ToString(row["DISTRIBUTOR"]); } else { siteid += "," + row["DISTRIBUTOR"]; } } } } else if (ddlSiteId.SelectedIndex > 0) { cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value); } else if (ddlSiteId.SelectedItem.Text != "All...") { cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value); } else { if (Convert.ToString(Session["LOGINTYPE"]) == "3") { string siteid = ""; DataTable 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 (siteid == "") { siteid = Convert.ToString(row["DISTRIBUTOR"]); } else { siteid += "," + row["DISTRIBUTOR"]; } } cmd.Parameters.AddWithValue("@SiteId", siteid); } else { cmd.Parameters.AddWithValue("@SiteId", ""); } } if (DDLBusinessUnit.SelectedIndex >= 1) { cmd.Parameters.AddWithValue("@BUCODE", DDLBusinessUnit.SelectedItem.Value.ToString()); } else { cmd.Parameters.AddWithValue("@BUCODE", ""); } dtDataByfilter = new DataTable(); cmd.Connection = objGlobal.GetConnection(); dtDataByfilter.Load(cmd.ExecuteReader()); LoadDataInReportViewer(dtDataByfilter); } catch (Exception ex) { this.LblMessage.Visible = true; this.LblMessage.Text = "► " + ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } finally { objGlobal.CloseSqlConnection(); } }
private void ShowReportByFilter() { CreamBell_DMS_WebApps.App_Code.Global objGlobal = new CreamBell_DMS_WebApps.App_Code.Global(); SqlConnection conn = null; SqlCommand cmd = null; DataTable dtDataByfilter = null; //string query = string.Empty; try { //conn = new SqlConnection(objGlobal.GetConnectionString()); //conn.Open(); cmd = new SqlCommand(); //cmd.Connection = conn; cmd.CommandTimeout = 3600; cmd.CommandType = CommandType.StoredProcedure; //query = "dbo.ACX_USP_TargetAchivementReport"; //cmd.CommandText = query; cmd.CommandText = "dbo.ACX_USP_TargetAchivementReport"; cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(txtFromDate.Text)); cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text)); cmd.Parameters.AddWithValue("@UserType", Convert.ToString(Session["LOGINTYPE"])); cmd.Parameters.AddWithValue("@UserCode", Convert.ToString(Session["USERID"])); // state if (ddlState.SelectedIndex < 0) { cmd.Parameters.AddWithValue("@StateId", ""); } if (ddlState.SelectedIndex > 0) { cmd.Parameters.AddWithValue("@StateId", ddlState.SelectedItem.Value); } else if (ddlState.SelectedItem.Text != "All...") { cmd.Parameters.AddWithValue("@StateId", ddlState.SelectedItem.Value); } else { cmd.Parameters.AddWithValue("@StateId", ""); } // site if (ddlSiteId.SelectedIndex < 0) { if (Convert.ToString(Session["LOGINTYPE"]) == "3") { string siteid = ""; DataTable 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 (siteid == "") { siteid = Convert.ToString(row["DISTRIBUTOR"]); } else { siteid += "," + row["DISTRIBUTOR"]; } } } else { cmd.Parameters.AddWithValue("@SiteId", ""); } } else if (ddlSiteId.SelectedIndex > 0) { cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value); } else if (ddlSiteId.SelectedItem.Text != "All...") { cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value); } else { if (Convert.ToString(Session["LOGINTYPE"]) == "3") { string siteid = ""; DataTable 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 (siteid == "") { siteid = Convert.ToString(row["DISTRIBUTOR"]); } else { siteid += "," + row["DISTRIBUTOR"]; } } cmd.Parameters.AddWithValue("@SiteId", siteid); } else { cmd.Parameters.AddWithValue("@SiteId", ""); } } if (drpCategory.SelectedIndex >= 1) { cmd.Parameters.AddWithValue("@TargetCategory", drpCategory.SelectedItem.Value); } else { cmd.Parameters.AddWithValue("@TargetCategory", ""); } if (drpSubCategory.SelectedIndex >= 1) { cmd.Parameters.AddWithValue("@TargetSubCategory", drpSubCategory.SelectedItem.Value); } else { cmd.Parameters.AddWithValue("@TargetSubCategory", ""); } if (drpCustGroup.SelectedIndex >= 1) { cmd.Parameters.AddWithValue("@ObjectCode", drpCustGroup.SelectedItem.Value); } else { cmd.Parameters.AddWithValue("@ObjectCode", ""); } if (drpCustomerName.SelectedIndex >= 1) { cmd.Parameters.AddWithValue("@targetsubobject", drpCustomerName.SelectedItem.Value); } else { cmd.Parameters.AddWithValue("@targetsubobject", ""); } if (DDLBusinessUnit.SelectedIndex >= 1) { cmd.Parameters.AddWithValue("@BUCODE", DDLBusinessUnit.SelectedItem.Value.ToString()); } else { cmd.Parameters.AddWithValue("@BUCODE", ""); } dtDataByfilter = new DataTable(); cmd.Connection = objGlobal.GetConnection(); dtDataByfilter.Load(cmd.ExecuteReader()); LoadDataInReportViewer(dtDataByfilter); } catch (Exception ex) { this.LblMessage.Visible = true; this.LblMessage.Text = "► " + ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } finally { objGlobal.CloseSqlConnection(); //if (conn != null) //{ // if (conn.State.ToString() == "Open") { conn.Close(); } //} } }