Esempio n. 1
0
        protected void DDLCustGroup_SelectedIndexChanged(object sender, EventArgs e)
        {
            CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();

            if (DDLCustGroup.Text == "ALL")
            {
                string queryALLCustomer = " Select CUSTOMER_CODE, CUSTOMER_NAME from ax.acxcustmaster where  SITE_CODE='" + Session["SiteCode"].ToString() +
                                          "' and DATAAREAID='" + Session["DATAAREAID"].ToString() + "'" +
                                          " UNION " +
                                          "SELECT SUBDISTRIBUTOR AS Customer_Code, SUBDISTRIBUTOR + '-' + NAME AS CUSTOMER_NAME  from ax.ACX_SDLINKING where OTHER_SITE='" + Session["SiteCode"].ToString() +
                                          "' and DATAAREAID='" + Session["DATAAREAID"].ToString() + "'";

                DDLCustomers.Items.Clear();
                //DDLCustomers.Items.Add("-Select-");
                //obj.BindToDropDown(DDLCustomers, queryALLCustomer, "CUSTOMER_NAME", "CUSTOMER_CODE");
            }
            else
            {
                string queryCustomer = " Select CUSTOMER_CODE, CUSTOMER_NAME from ax.acxcustmaster where CUST_GROUP='" + DDLCustGroup.SelectedValue.ToString() + "' " +
                                       " and SITE_CODE='" + Session["SiteCode"].ToString() + "' and DATAAREAID='" + Session["DATAAREAID"].ToString() + "'" +
                                       " UNION " +
                                       "SELECT SUBDISTRIBUTOR AS Customer_Code, SUBDISTRIBUTOR + '-' + NAME AS CUSTOMER_NAME  from ax.ACX_SDLINKING where CUSTGROUP='" + DDLCustGroup.SelectedValue.ToString() + "' " +
                                       " and OTHER_SITE='" + Session["SiteCode"].ToString() + "' and DATAAREAID='" + Session["DATAAREAID"].ToString() + "'";

                //DDLCustomers.Items.Clear();
                //DDLCustomers.Items.Add("-Select-");
                //obj.BindToDropDown(DDLCustomers, queryCustomer, "CUSTOMER_NAME", "CUSTOMER_CODE");
                CreamBell_DMS_WebApps.App_Code.CreamBellFramework.BindToDropDown(DDLCustomers, queryCustomer, "CUSTOMER_NAME", "CUSTOMER_CODE", "-Select-");
            }
        }
Esempio n. 2
0
        private void BindFilters()
        {
            CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();

            string queryCustomerGroup = " Select CUSTGROUP_CODE, CUSTGROUP_NAME, CUSTGROUP_CODE+'-'+ CUSTGROUP_NAME as CUSTGROUP from [ax].[ACXCUSTGROUPMASTER] where DATAAREAID='" + Session["DATAAREAID"].ToString() + "' and BLOCKED<>1 ";

            //DDLCustGroup.Items.Clear();
            //DDLCustGroup.Items.Add("ALL");
            //obj.BindToDropDown(DDLCustGroup, queryCustomerGroup, "CUSTGROUP", "CUSTGROUP_CODE");
            CreamBell_DMS_WebApps.App_Code.CreamBellFramework.BindToDropDown(DDLCustGroup, queryCustomerGroup, "CUSTGROUP", "CUSTGROUP_CODE", "ALL");
            string query = "select bm.bu_code,bu_desc from ax.acxsitebumapping sbp join ax.ACXBUMASTER bm on bm.bu_code = sbp.BU_CODE where SITEID = '" + Convert.ToString(Session["SiteCode"]) + "'";

            //DDLBusinessUnit.Items.Clear();
            //DDLBusinessUnit.Items.Add("All...");
            //obj.BindToDropDown(DDLBusinessUnit, query, "bu_desc", "bu_code");
            CreamBell_DMS_WebApps.App_Code.CreamBellFramework.BindToDropDown(DDLBusinessUnit, query, "bu_desc", "bu_code", "All...");
        }
Esempio n. 3
0
        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 query = "Select NAME from ax.inventsite where SITEID='" + Session["SiteCode"].ToString() + "'";
                DataTable  dtSetHeader = new DataTable();
                DataColumn dc          = new DataColumn(SessionKeys.NAME);
                dtSetHeader.Columns.Add(dc);
                dtSetHeader.Rows.Add(Session[SessionKeys.NAME].ToString());
                //dtSetHeader = obj.GetData(query);
                string CustomerGroup = string.Empty;
                string Customer      = string.Empty;
                string BU            = string.Empty;
                if (DDLCustGroup.SelectedIndex > 0)
                {
                    CustomerGroup = DDLCustGroup.SelectedValue.ToString();
                }
                else
                {
                    CustomerGroup = "";
                }
                if (DDLCustomers.SelectedIndex > 0)
                {
                    Customer = DDLCustomers.SelectedValue.ToString();
                }
                else
                {
                    Customer = "";
                }
                if (DDLBusinessUnit.SelectedIndex >= 1)
                {
                    BU = DDLBusinessUnit.SelectedItem.Value.ToString();
                }
                else
                {
                    BU = "";
                }

                //FilterQuery = "EXEC SP_PARTYWISESALESUMMARY '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "','" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "','" + CustomerGroup + "','" + Customer + "','" + BU + "'";
                FilterQuery = "[SP_PARTYWISESALESUMMARY_TI_Version]"; //"SP_PARTYWISESALESUMMARY";
                //+ Session["SiteCode"].ToString() + "','"
                //+ Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "','"
                //+ Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "','"
                //+ CustomerGroup + "','"
                //+ Customer + "','"
                //+ BU + "'";

                //FilterQuery = " SELECT SITEID, INVOICE_NO, SIW.CUSTOMER_NAME,C.CUST_GROUP,"
                //             + " isnull(BOXQty,'0') as Box,  isnull(PCSQTY,'0') as PCS, isnull(BOXPCS,'0') as [TotalBoxPCS],  BOX as TotalQtyConv, SIW.LTR, LINEAMOUNT, DISC_AMOUNT, SEC_DISC_AMOUNT, DISC, TAX_CODE, TAX_AMOUNT,  ADDTAX_CODE, "
                //             + " ADDTAX_AMOUNT, AMOUNT,TD_Per,PE_Per,tdvalue FROM ACX_SALESUMMARY_PARTY_ITEM_WISE SIW"
                //             + " INNER JOIN AX.INVENTTABLE INVT ON SIW.PRODUCT_CODE = INVT.ITEMID "
                //             + " LEFT JOIN [ax].[ACXCUSTMASTER] C on SIW.Customer_Code = C.CUSTOMER_CODE"
                //             + " where SITEID = '" + Session["SiteCode"].ToString() + "' and INVOICE_DATE >=" +
                //              " '" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "' and  INVOICE_DATE <='" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "' " +
                //              " ORDER BY INVOICE_DATE ASC , INVOICE_NO ASC ";
                List <string> param    = new List <string>();
                List <string> paramVal = new List <string>();
                param.Add("@SiteId");
                paramVal.Add(Session["SiteCode"].ToString());

                param.Add("@StartDate");
                paramVal.Add(Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd"));

                param.Add("@EndDate");
                paramVal.Add(Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd"));

                param.Add("@customergroupname");
                paramVal.Add(CustomerGroup);

                param.Add("@customername");
                paramVal.Add(Customer);

                param.Add("@BUCODE");
                paramVal.Add(BU);

                DataTable dtSetData = obj.GetData_New(FilterQuery, CommandType.StoredProcedure, param, paramVal);
                //dtSetData = new DataTable();
                //dtSetData = obj.GetData(FilterQuery);

                string queryTotInv = " Select Count(Distinct INVOICE_NO) as InvoiceNo FROM ACX_SALESUMMARY_PARTY_ITEM_WISE " +
                                     " where SITEID = '" + Session["SiteCode"].ToString() + "' and INVOICE_DATE >=" +
                                     " '" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "' and  INVOICE_DATE <='" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "' group by CUSTOMER_NAME";
                DataTable dtTotalInvoiceNo = obj.GetData(queryTotInv);
                LoadDataInReportViewer(dtSetHeader, dtSetData, dtTotalInvoiceNo);
            }
            catch (Exception ex)
            {
                LblMessage.Text = ex.Message.ToString();
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
        }
Esempio n. 4
0
        private void ExportToExcelNew()
        {
            string tempXlsxFilePath = string.Empty;
            string tempXlsbFilePath = string.Empty;

            try
            {
                bool b = ValidateInput();
                if (b == true)
                {
                    GridView gvvc = new GridView();
                    CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();

                    if (lstState.SelectedValue == string.Empty)
                    {
                        string message = "alert('Please Select The SiteID  !');";
                        ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", message, true);
                    }
                    string siteid, StateCode;
                    string FromDate = txtFromDate.Text;
                    string ToDate   = txtToDate.Text;
                    siteid = StateCode = "";
                    foreach (System.Web.UI.WebControls.ListItem litem in lstState.Items)
                    {
                        if (litem.Selected)
                        {
                            if (StateCode.Length == 0)
                            {
                                StateCode = "" + litem.Value.ToString() + "";
                            }
                            else
                            {
                                StateCode += "," + litem.Value.ToString() + "";
                            }
                        }
                    }
                    foreach (System.Web.UI.WebControls.ListItem litem in lstSiteId.Items)
                    {
                        if (litem.Selected)
                        {
                            if (siteid.Length == 0)
                            {
                                siteid = "" + litem.Value.ToString() + "";
                            }
                            else
                            {
                                siteid += "," + litem.Value.ToString() + "";
                            }
                        }
                    }
                    string query = "EXEC ACX_USP_OpenSaleOrder '" + StateCode + "','" + siteid.ToString() + "','" + Session["DATAAREAID"].ToString() + "','" + FromDate + "','" + ToDate + "','" + Convert.ToString(Session["USERID"]) + "','" + Convert.ToString(Session["LOGINTYPE"]) + "'";

                    //string query = "EXEC ACX_USP_OpenSaleOrder '" + StateCode + "','" + siteid.ToString() + "','" + Session["DATAAREAID"].ToString() + "','" + FromDate + "','" + ToDate + "'";

                    using (SqlCommand cmd = new SqlCommand(query, obj.GetConnection()))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            //cmd.Connection = obj.GetConnection();
                            cmd.CommandTimeout = 3600;
                            sda.SelectCommand  = cmd;
                            using (DataTable dt = new DataTable())
                            {
                                sda.Fill(dt);
                                using (XLWorkbook wb = new XLWorkbook())
                                {
                                    wb.Worksheets.Add(dt, "Customers");
                                    Response.Clear();
                                    Response.Buffer  = true;
                                    Response.Charset = "";
                                    if ("XLSX".CompareTo(rdbListExcelFileFormat.SelectedValue) == 0)
                                    {
                                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //xlsx
                                        Response.AddHeader("content-disposition", "attachment;filename=SaleOrderDetailsReport.xlsx");
                                        using (MemoryStream MyMemoryStream = new MemoryStream())
                                        {
                                            wb.SaveAs(MyMemoryStream);
                                            MyMemoryStream.WriteTo(Response.OutputStream);
                                        }
                                    }
                                    if ("XLS".CompareTo(rdbListExcelFileFormat.SelectedValue) == 0)
                                    {
                                        Response.ContentType = "application/vnd.ms-excel"; // xls
                                        Response.AddHeader("content-disposition", "attachment;filename=SaleOrderDetailsReport.xls");
                                        using (MemoryStream MyMemoryStream = new MemoryStream())
                                        {
                                            wb.SaveAs(MyMemoryStream);
                                            MyMemoryStream.WriteTo(Response.OutputStream);
                                        }
                                    }

                                    if ("XLSB".CompareTo(rdbListExcelFileFormat.SelectedValue) == 0)
                                    {
                                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //xlsb
                                        Response.AddHeader("content-disposition", "attachment;filename=SaleOrderDetailsReport.xlsb");
                                        var tempFile = DateTime.Now.Ticks.ToString();
                                        tempXlsxFilePath = Server.MapPath("xlfiles/tempXlsxFile_" + tempFile + ".xlsx");
                                        tempXlsbFilePath = Server.MapPath("xlfiles/tempXlsbFile_" + tempFile + ".xlsb");
                                        //CreamBellFramework.ExportToXLSX(tempXlsbFilePath, dt, "Customers");
                                        //wb.SaveAs(tempXlsxFilePath);
                                        //ConvertToXLSBFile(tempXlsxFilePath, tempXlsbFilePath);
                                        Response.WriteFile(tempXlsbFilePath);
                                    }
                                }
                            }
                        }
                    }
                    Response.Flush();
                    Response.End();
                }
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
                CleanFiles(tempXlsxFilePath, tempXlsbFilePath);
            }
            finally
            {
                CleanFiles(tempXlsxFilePath, tempXlsbFilePath);
            }
        }
Esempio n. 5
0
        private void ShowData_ForExcel()
        {
            CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
            string        FilterQuery = string.Empty;
            SqlConnection conn        = null;
            SqlCommand    cmd         = null;
            string        query       = string.Empty;
            bool          endRequest  = false;

            try
            {
                conn = new SqlConnection(obj.GetConnectionString());
                conn.Open();
                cmd                = new SqlCommand();
                cmd.Connection     = conn;
                cmd.CommandTimeout = 0;
                cmd.CommandType    = CommandType.Text;

                if (rdWorking.Checked)
                {
                    query = "GetDataForUseNonuseReport '" + txtFromDate.Text + "','" + txtToDate.Text + "','" + ddlstate.SelectedValue + "','" + rdWorking.Text + "'";
                }
                else if (rdNotWorking.Checked)
                {
                    query = "GetDataForUseNonuseReport '" + txtFromDate.Text + "','" + txtToDate.Text + "','" + ddlstate.SelectedValue + "','" + rdNotWorking.Text + "'";
                }
                else
                {
                    query = "GetDataForUseNonuseReport '" + txtFromDate.Text + "','" + txtToDate.Text + "','" + ddlstate.SelectedValue + "'";
                }

                cmd.CommandText = query;
                //cmd.Parameters.AddWithValue("@SITEID", Session["SiteCode"].ToString());
                //cmd.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
                DataTable dt = new DataTable();
                dt = new DataTable();
                dt.Load(cmd.ExecuteReader());

                //=================Create Excel==========

                //var workbook = new ExcelFile();

                //// Add new worksheet to Excel file.
                //var worksheet = workbook.Worksheets.Add("New worksheet");

                //// Set the value of the cell "A1".
                //worksheet.Cells["A1"].Value = "Hello world!";

                //// Save Excel file.
                //workbook.Save("Workbook.xls");
                string attachment = "attachment; filename=Usenousereport.xls";
                Response.ClearContent();
                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);

                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)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }

            finally
            {
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
        }
        private DataTable GetData(string strProcName, string strReportName, CreamBell_DMS_WebApps.App_Code.Global obj, ref DataTable dtSetHeader)
        {
            string query1 = "Select NAME from ax.inventsite where SITEID='" + Session["SiteCode"].ToString() + "'";

            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              = strProcName;
            cmd.CommandText    = query;
            DateTime now = Convert.ToDateTime(txtToDate.Text);

            now = now.AddMonths(1);

            cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(txtFromDate.Text));
            cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text));

            string SiteList = "";

            foreach (ListItem item in chkListSite.Items)
            {
                if (item.Selected)
                {
                    if (SiteList == "")
                    {
                        SiteList += "" + item.Value.ToString() + "";
                    }
                    else
                    {
                        SiteList += "," + item.Value.ToString() + "";
                    }
                }
            }
            if (SiteList.Length > 0)
            {
                cmd.Parameters.AddWithValue("@SITEID", SiteList);
            }
            else
            {
                cmd.Parameters.AddWithValue("@SITEID", "");
            }

            string strStateCode = "";

            foreach (ListItem item in chkListState.Items)
            {
                if (item.Selected)
                {
                    if (strStateCode == "")
                    {
                        strStateCode += "" + item.Value.ToString() + "";
                    }
                    else
                    {
                        strStateCode += "," + item.Value.ToString() + "";
                    }
                }
            }
            if (strStateCode.Length > 0)
            {
                cmd.Parameters.AddWithValue("@STATECODE", strStateCode);
            }
            else
            {
                cmd.Parameters.AddWithValue("@STATECODE", "");
            }
            cmd.Parameters.Add("@SupplyCode", SqlDbType.NVarChar, 40000).Direction = ParameterDirection.Output;

            dtDataByfilter = new DataTable();
            dtDataByfilter.Load(cmd.ExecuteReader());
            DataTable dt = new DataTable();

            dt           = dtDataByfilter;
            SupplierCode = Convert.ToString(cmd.Parameters["@SupplyCode"].Value);


            return(dt);
        }