protected void FillState() { string sqlstr = "select * from ax.ACXSITEMENU where SITE_CODE ='" + Session["SiteCode"].ToString() + "'"; object objcheckSitecode = baseObj.GetScalarValue(sqlstr); DataTable dt = new DataTable(); dt = new DataTable(); if (objcheckSitecode != null) { chkListState.Items.Clear(); string sqlstr11 = "Select Distinct I.StateCode Code,I.StateCode+'-'+LS.Name Name from [ax].[INVENTSITE] I left join [ax].[LOGISTICSADDRESSSTATE] LS on LS.STATEID = I.STATECODE where I.STATECODE <>'' "; dt = baseObj.GetData(sqlstr11); //chkListState.Items.Add("All..."); chkListState.DataSource = dt; chkListState.DataTextField = "NAME"; chkListState.DataValueField = "Code"; chkListState.DataBind(); //chkListState.Items.Insert(0, new ListItem("All...")); //for (int i = 0; i < dt.Rows.Count; i++) //{ // chkListState.DataSource = dt; // chkListState.DataTextField = "NAME"; // chkListState.DataValueField = "Code"; // chkListState.DataBind(); //} } else { chkListState.Items.Clear(); chkListSite.Items.Clear(); string sqlstr1 = @"Select I.StateCode StateCode,I.StateCode+'-'+LS.Name as StateName,I.SiteId,I.SiteId+'-'+I.Name as SiteName from [ax].[INVENTSITE] I left join [ax].[LOGISTICSADDRESSSTATE] LS on LS.STATEID = I.STATECODE where I.SiteId = '" + Session["SiteCode"].ToString() + "'"; dt = baseObj.GetData(sqlstr1); chkListState.DataSource = dt; chkListState.DataTextField = "StateName"; chkListState.DataValueField = "StateCode"; chkListState.DataBind(); chkListSite.DataSource = dt; chkListSite.DataTextField = "SiteName"; chkListSite.DataValueField = "SiteId"; chkListSite.DataBind(); //for (int i = 0; i < dt.Rows.Count; i++) //{ // chkListState.DataSource = dt; // chkListState.DataTextField = "StateName"; // chkListState.DataValueField = "StateCode"; // chkListState.DataBind(); // chkListSite.DataSource = dt; // chkListSite.DataTextField = "SiteName"; // chkListSite.DataValueField = "SiteId"; // chkListSite.DataBind(); //} if (dt.Rows.Count > 0) { chkListState.Items[0].Selected = true; chkListSite.Items[0].Selected = true; } } }
protected void Page_Load(object sender, EventArgs e) { LblMessage.Text = ""; if (Session["USERID"] == null || Session["USERID"].ToString() == string.Empty) { Response.Redirect("Login.aspx"); return; } if (!IsPostBack) { if (Session["SiteCode"] != null) { //==================For Warehouse Location============== query = "select MainWarehouse from ax.inventsite where siteid='" + Session["SiteCode"].ToString() + "'"; DataTable dt = new DataTable(); dt = obj.GetData(query); if (dt.Rows.Count > 0) { Session["TransLocation"] = dt.Rows[0]["MainWarehouse"].ToString(); } } GetReceipt(); GetReason(); } }
private void GetReceipt() { try { query = "EXEC ACX_GET_NONSALABLEINVOICE '" + Session["SiteCode"].ToString() + "','" + Session["DATAAREAID"].ToString() + "',0"; dt = new DataTable(); dt = obj.GetData(query); if (dt.Rows.Count > 0) { drpRecieptNo.DataSource = dt; drpRecieptNo.DataTextField = "PURCH_RECIEPTNO"; drpRecieptNo.DataValueField = "PURCH_RECIEPTNO"; drpRecieptNo.DataBind(); drpRecieptNo.Items.Insert(0, new ListItem("--Select--", "0")); drpInvoceNo.DataSource = dt; drpInvoceNo.DataTextField = "SALE_INVOICENO"; drpInvoceNo.DataValueField = "SALE_INVOICENO"; drpInvoceNo.DataBind(); drpInvoceNo.Items.Insert(0, new ListItem("--Select--", "0")); } } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); } }
protected void BindGrid(string sqlstr) { DataTable dt = new DataTable(); dt = baseObj.GetData(sqlstr); //callign a Global Class getData if (dt.Rows.Count > 0) { gvDetails.DataSource = dt; gvDetails.DataBind(); //Bind Sale Header } }
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; string CustomerGroup = string.Empty; string Customer = string.Empty; string BU = string.Empty; if (DDLCustGroupNew.SelectedIndex > 0) { CustomerGroup = DDLCustGroupNew.SelectedValue.ToString(); } else { CustomerGroup = ""; } if (DDLCustomersNew.SelectedIndex > 0) { Customer = DDLCustomersNew.SelectedValue.ToString(); } else { Customer = ""; } if (DDLBusinessUnitNew.SelectedIndex >= 1) { BU = DDLBusinessUnitNew.SelectedItem.Value.ToString(); } else { BU = ""; } try { string query = "Select NAME from ax.inventsite where SITEID IN (" + ucRoleFilters.GetCommaSepartedSiteId() + ") "; dtSetHeader = new DataTable(); dtSetHeader = obj.GetData(query); FilterQuery = "EXEC SP_SALEREGISTERINVOICEWISE '" + ucRoleFilters.GetCommaSepartedSiteId() + "','" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "','" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "','" + CustomerGroup + "','" + Customer + "','" + BU + "'"; dtSetData = new DataTable(); dtSetData = obj.GetData(FilterQuery); LoadDataInReportViewer(dtSetHeader, dtSetData); } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } }
protected void BindGridview() { try { string condition = string.Empty; if (drpCustomerGroup.SelectedItem.Value == "--Select--") { //this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Select Customer Group');", true); LblMessage.Text = "Select Customer Group !"; LblMessage.Visible = true; uppanel.Update(); return; } //if (drpCustomerName.SelectedItem.Value != "--Select--") //{ // condition = " And A.[CUSTOMER_CODE]='" + drpCustomerName.SelectedItem.Value + "'"; //} //query = "select A.[CUSTOMER_CODE],A.[CUSTOMER_CODE]+'-'+A.[CUSTOMER_NAME] as Customer" + // " ,A.[ADDRESS1]+','+A.[ADDRESS2]+','+A.[CITY]+','+A.[AREA]+','+A.[DISTRICT]+','+A.[STATE]+','+A.[ZIPCODE] as Address " + // " from [ax].[ACXCUSTMASTER] A " + // " where A.[SITE_CODE]='" + Session["SiteCode"].ToString() + "' and A.[DATAAREAID]='" + Session["DATAAREAID"].ToString() + "' and A.Blocked=0 and A.[CUST_GROUP]='" + drpCustomerGroup.SelectedItem.Value + "' " + condition + // " order by Customer"; if (drpCustomerName.SelectedItem.Value != "--Select--") { query = "EXEC Acx_GetCustomerDetailsWithSDLinking '" + Session["SiteCode"].ToString() + "','" + drpCustomerGroup.SelectedValue + "','" + drpCustomerName.SelectedItem.Value + "' "; dt = new DataTable(); dt = baseObj.GetData(query); } else { query = "EXEC Acx_GetCustomerDetailsWithSDLinking '" + Session["SiteCode"].ToString() + "','" + drpCustomerGroup.SelectedValue + "','' "; dt = new DataTable(); dt = baseObj.GetData(query); } if (dt.Rows.Count > 0) { gvDetails.DataSource = dt; gvDetails.DataBind(); } else { gvDetails.DataSource = null; gvDetails.DataBind(); } } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); } }
protected void DDLProductDesc_SelectedIndexChanged(object sender, EventArgs e) { //===========Fill Product Group and Product Sub Cat======== DataTable dt = new DataTable(); string query = "select Product_Group,PRODUCT_SUBCATEGORY from ax.INVENTTABLE where ItemId='" + DDLProductDesc.SelectedValue.ToString() + "' order by Product_Name"; dt = baseObj.GetData(query); if (dt.Rows.Count > 0) { DDLProductGroup.Text = dt.Rows[0]["PRODUCT_GROUP"].ToString(); ProductSubCategory(); //=============For Product Sub Cat====== DDLProdSubCategory.Text = dt.Rows[0]["PRODUCT_SUBCATEGORY"].ToString(); CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); string queryStock = " Select isnull(SUM(INVTS.TRANSQTY),0) AS STOCK From AX.ACXINVENTTRANS INVTS INNER JOIN AX.INVENTTABLE INV ON INV.ITEMID= INVTS.PRODUCTCODE " + " WHERE INVTS.DATAAREAID='" + Session["DATAAREAID"] + "' AND INVTS.SiteCode='" + Session["SiteCode"] + "' " + " AND INVTS.TRANSLOCATION='" + DDLWarehouseFrom.SelectedValue.Trim().ToString() + "' " + " AND INVTS.PRODUCTCODE ='" + DDLProductDesc.SelectedValue.ToString() + "'"; object stockValue = obj.GetScalarValue(queryStock); if (stockValue != null) { txtstock.Text = Convert.ToDecimal(stockValue.ToString()).ToString("0.00"); } } }
protected void BtnGetProductDetails_Click(object sender, EventArgs e) { if (txtProductCode.Text != string.Empty) { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); string queryFillProductDetails = "Select ITEMID +'-(' + PRODUCT_NAME+')' as PRODUCT_NAME, ITEMID,PRODUCT_GROUP, PRODUCT_SUBCATEGORY " + "from ax.INVENTTABLE where replace(replace(ITEMID, char(9), ''), char(13) + char(10), '')= '" + txtProductCode.Text.Trim().ToString() + "'"; DataTable dtProductDetails = obj.GetData(queryFillProductDetails); if (dtProductDetails.Rows.Count > 0 && dtProductDetails.Rows.Count == 1) { DDLProductGroup.Items.Clear(); DDLProdSubCategory.Items.Clear(); DDLProductDesc.Items.Clear(); DDLProductGroup.Items.Add(dtProductDetails.Rows[0]["PRODUCT_GROUP"].ToString()); DDLProductGroup.SelectedItem.Text = dtProductDetails.Rows[0]["PRODUCT_GROUP"].ToString(); DDLProdSubCategory.Items.Add(dtProductDetails.Rows[0]["PRODUCT_SUBCATEGORY"].ToString()); DDLProdSubCategory.SelectedItem.Text = dtProductDetails.Rows[0]["PRODUCT_SUBCATEGORY"].ToString(); DDLProductDesc.Items.Add(dtProductDetails.Rows[0]["PRODUCT_NAME"].ToString()); DDLProductDesc.SelectedItem.Text = dtProductDetails.Rows[0]["PRODUCT_NAME"].ToString(); DDLProductGroup.Enabled = false; DDLProdSubCategory.Enabled = false; DDLProductDesc.Enabled = false; DDLReason.Focus(); } if (dtProductDetails.Rows.Count > 1) { DDLProductGroup.Items.Clear(); DDLProdSubCategory.Items.Clear(); DDLProductDesc.Items.Clear(); string message = "alert('Product Code Issue: We Have Duplicate Records for this Product Code !');"; ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", message, true); //this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Product Code Issue: We Have Duplicate Records for this Product Code !');", true); } if (dtProductDetails.Rows.Count == 0) { DDLProductGroup.Items.Clear(); DDLProdSubCategory.Items.Clear(); DDLProductDesc.Items.Clear(); string message = "alert('Product Code Issue: No Such Produt Code Exist !');"; ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", message, true); //this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Product Code Issue: No Such Produt Code Exist !');", true); } } else { txtProductCode.Focus(); string message = "alert('Please Provide Product Code Here !');"; ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", message, true); //this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Please Provide Product Code Here !');", true); } }
protected void FillCustomerGroup() { DataTable dt = new DataTable(); string sqlstr = "select Distinct CustGroup_Name as Name,Custgroup_Code as Code from ax.ACXCUSTGROUPMASTER "; dt = new DataTable(); dt = baseObj.GetData(sqlstr); chkListCustomerGroup.Items.Clear(); for (int i = 0; i < dt.Rows.Count; i++) { chkListCustomerGroup.DataSource = dt; chkListCustomerGroup.DataTextField = "NAME"; chkListCustomerGroup.DataValueField = "Code"; chkListCustomerGroup.DataBind(); } }
protected void ddlVrs_SelectedIndexChanged(object sender, EventArgs e) { query = "exec ACX_GETVRS_MARKETRETURN '" + Session["SiteCode"].ToString() + "','" + ddlVrs.SelectedValue.ToString() + "'"; dt = obj.GetData(query); Session["MarketVRSDetails"] = dt; Session["VRSProductDetails"] = null; GetProduct(); }
protected void GetProduct() { #region Product Details string bucode; if (ddlBusinessUnit.SelectedIndex == 0) { bucode = ""; } else { bucode = ddlBusinessUnit.SelectedValue; } dt = new DataTable(); if (rdoColdRoomDistruction.Checked == true) { query = "exec ACX_GETDISTRUCTIONPRODUCTDETAILS '" + Session["SiteCode"].ToString() + "',0,'" + bucode + "'"; dt = obj.GetData(query); } else if (rdoMarketReturnDistruction.Checked == true) { // query = "exec ACX_GETDISTRUCTIONPRODUCTDETAILS '" + Session["SiteCode"].ToString() + "',1"; dt = (DataTable)Session["MarketDistructionDetails"]; if (dt != null) { var Value = dt.Select("Cust_Group ='" + ddlCustomerGroup.Text.Trim() + "' AND CUSTOMER_CODE ='" + ddlCustomer.Text.Trim() + "'"); dt = Value.CopyToDataTable(); var distValue = dt.DefaultView.ToTable(true, "PRODUCT_NAME", "PRODUCTCODE"); DataTable dtCust = new DataTable(); dtCust = (DataTable)distValue; dt = dtCust; } } if (dt != null) { DDLMaterialCode.DataSource = dt; DDLMaterialCode.DataTextField = "PRODUCT_NAME"; DDLMaterialCode.DataValueField = "PRODUCTCODE"; DDLMaterialCode.DataBind(); DDLMaterialCode.Items.Insert(0, new ListItem("--Select--", "0")); } #endregion }
private void GetObjectType() { try { query = "select [TARGETOBJECT_TYPE],[TARGETOBJECTTYPE_NAME] from [ax].[ACXTARGETOBJECTMASTER] where [DATAAREAID]='" + Session["DATAAREAID"].ToString() + "'"; DataTable dt = new DataTable(); dt = obj.GetData(query); if (dt.Rows.Count > 0) { drpTargetObject.DataSource = dt; drpTargetObject.DataTextField = "TARGETOBJECTTYPE_NAME"; drpTargetObject.DataValueField = "TARGETOBJECT_TYPE"; drpTargetObject.DataBind(); drpTargetObject.Items.Insert(0, new ListItem("--Select--", "0")); } } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); } }
public void Bind_Grid() { try { // string sqlstr = @"Select Distinct SCHEMECODE,[SCHEME DESCRIPTION] as Name,Case when [SCHEME TYPE]=0 then 'Quantity' when [SCHEME TYPE] = 1 then 'Value' end as [SCHEME TYPE],STARTINGDATE,ENDINGDATE from [dbo].[ACXSCHEMEVIEW] // Where SALESCODE in ( '" + Session["SiteCode"].ToString() + "','" + Session["SITELOCATION"].ToString() + "','') "; string sqlstr = @"Select Distinct SCHEMECODE,[SCHEME DESCRIPTION] as Name,Case when [SCHEME TYPE]=0 then 'Quantity' when [SCHEME TYPE] = 1 then 'Value' when [SCHEME TYPE] = 2 then 'Percent' when [SCHEME TYPE] = 3 then 'Valueoff' end as [SCHEME TYPE],STARTINGDATE,ENDINGDATE from [dbo].[ACXSCHEMEVIEW] Where SALESCODE in ( '" + ddlSiteId.SelectedItem.Value + "','" + ddlState.SelectedItem.Value + "','') "; DataTable dt = baseObj.GetData(sqlstr); gvSchemeDetail.DataSource = dt; gvSchemeDetail.DataBind(); } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); } finally { if (conn != null) { if (conn.State.ToString() == "Closed") { conn.Open(); } } } }
protected void fillSiteAndState(DataTable dt) { string sqlstr = ""; if (Convert.ToString(Session["ISDISTRIBUTOR"]) == "Y") { if (Convert.ToString(Session["LOGINTYPE"]) == "3") { DataTable dtState = dt.DefaultView.ToTable(true, "STATE", "STATENAME"); dtState.Columns.Add("STATENAMES", typeof(string), "STATE + ' - ' + STATENAME"); lstState.Items.Clear(); DataRow dr = dtState.NewRow(); lstState.DataSource = dtState; lstState.DataTextField = "STATENAMES"; lstState.DataValueField = "STATE"; lstState.DataBind(); } else { sqlstr = "Select Distinct I.StateCode Code,I.StateCode + ' - ' + LS.Name AS Name from [ax].[INVENTSITE] I left join [ax].[LOGISTICSADDRESSSTATE] LS on LS.STATEID = I.STATECODE where I.STATECODE <>'' AND I.SITEID='" + Convert.ToString(Session["SiteCode"]) + "' order by Name"; //ddlState.Items.Add("Select..."); // baseObj.BindToDropDown(ddlState, sqlstr, "Name", "Code"); DataTable dt1 = baseObj.GetData(sqlstr); lstState.DataSource = dt1; lstState.DataTextField = "Name"; lstState.DataValueField = "Code"; lstState.DataBind(); } } else { sqlstr = "Select Distinct I.StateCode Code,I.StateCode + ' - ' + LS.Name AS Name from [ax].[INVENTSITE] I left join [ax].[LOGISTICSADDRESSSTATE] LS on LS.STATEID = I.STATECODE where I.STATECODE <>'' order by Name "; lstState.Items.Add("Select..."); // baseObj.BindToDropDown(ddlState, sqlstr, "Name", "Code"); DataTable dt1 = baseObj.GetData(sqlstr); lstState.DataSource = dt1; lstState.DataTextField = "Name"; lstState.DataValueField = "Code"; lstState.DataBind(); } if (lstState.Items.Count == 1) { foreach (System.Web.UI.WebControls.ListItem litem in lstState.Items) { litem.Selected = true; } lstState_SelectedIndexChanged(null, null); } }
private DataTable fillSiteCode() { DataTable dt = new DataTable(); try { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); string strQuery = "Select Distinct SITEID as Code,NAME from [ax].[INVENTSITE] where SITEID IN (" + ucRoleFilters.GetCommaSepartedSiteId() + ") "; dt = obj.GetData(strQuery); } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); } return(dt); }
private DataTable fillStateCode() { DataTable dt = new DataTable(); try { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); string strQuery = "Select distinct I.StateCode as Code,LS.Name from [ax].[INVENTSITE] I left join [ax].[LOGISTICSADDRESSSTATE] LS on LS.STATEID = I.STATECODE where I.SiteId IN (" + ucRoleFilters.GetCommaSepartedSiteId() + ") "; dt = obj.GetData(strQuery); } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); } return(dt); }
private void ShowCollectionDetails() { try { string FromDate = txtFromDate.Text; string ToDate = txtToDate.Text; string condition = string.Empty; if (drpCustomerGroupNew.SelectedItem != null && drpCustomerGroupNew.Text != "--Select--") { condition = " and B.Cust_Group='" + drpCustomerGroupNew.SelectedItem.Value + "' "; } query = "select A.[Document_No],A.[Collection_Date],A.[Customer_Code],B.[CUSTOMER_NAME],B.[ADDRESS1]+','+B.[ADDRESS2]+','+B.[CITY]+','+B.[AREA]" + " +','+B.[DISTRICT]+','+B.[STATE]+','+B.[ZIPCODE] as Address,C.Instrument_Description,A.Instrument_No,A.Ref_DocumentNo,A.Collection_Amount " + " FROM [ax].[ACXCOLLECTIONENTRY] A" + //or uat//" Inner Join [ax].[ACXCUSTMASTER] B on B.Customer_Code=A.[Customer_Code] and A.Dataareaid=B.Dataareaid "+ //" Inner Join [ax].[ACXCUSTMASTER] B on B.Customer_Code=A.[Customer_Code] and A.SiteCode=B.Site_Code and A.Dataareaid=B.Dataareaid " + " Inner Join VW_CUSTOMERMASTER_HISTORY B on B.Customer_Code=A.[Customer_Code] and A.SiteCode=B.Site_Code and A.Dataareaid=B.Dataareaid " + " Inner join [ax].[ACXINSTRUMENTMASTER] C on C.Instrument_Code=A.Instrument_Code and A.Dataareaid=C.Dataareaid " + " Where A.SiteCode IN (" + ucRoleFilters.GetCommaSepartedSiteId() + ") and A.Dataareaid='" + Session["DATAAREAID"].ToString() + "' and A.[Collection_Date] between '" + FromDate + "' and '" + ToDate + "' " + condition + " order by A.[Customer_Code],A.[Document_No] "; //query = "select A.[Document_No],A.[Collection_Date],A.[Customer_Code],B.[CUSTOMER_NAME],B.[ADDRESS1]+','+B.[ADDRESS2]+','+B.[CITY]+','+B.[AREA]" + // " +','+B.[DISTRICT]+','+B.[STATE]+','+B.[ZIPCODE] as Address,C.Instrument_Description,A.Instrument_No,A.Ref_DocumentNo,A.Collection_Amount "+ // " FROM [ax].[ACXCOLLECTIONENTRY] A"+ // " Inner Join [ax].[ACXCUSTMASTER] B on B.Customer_Code=A.[Customer_Code] and A.SiteCode=B.Site_Code and A.Dataareaid=B.Dataareaid "+ // " Inner join [ax].[ACXINSTRUMENTMASTER] C on C.Instrument_Code=A.Instrument_Code and A.Dataareaid=C.Dataareaid "+ // " Where A.SiteCode='" + Session["SiteCode"].ToString() + "' and A.Dataareaid='" + Session["DATAAREAID"].ToString() + "' and A.[Collection_Date] between '" + FromDate + "' and '" + ToDate + "' " + condition + // " order by A.[Customer_Code],A.[Document_No] "; DataTable dt = obj.GetData(query); if (dt.Rows.Count > 0) { gvDetails.DataSource = dt; gvDetails.DataBind(); LblMessage.Text = "Total Records : " + dt.Rows.Count.ToString(); Session["SaleRegister"] = dt; } else { gvDetails.DataSource = dt; gvDetails.DataBind(); LblMessage.Text = string.Empty; } } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); } }
private bool ValidateStockTransferSave() // Validates all required values for Saving the Data to Database INVENTRANS// { bool _value = false; if (gridStockTransferItems.Rows.Count <= 0 && Session["ItemTable"] == null) { _value = false; this.LblMessage.Text = "► Please Add Stock Transfer Product !"; //ScriptManager.RegisterStartupScript(this, this.GetType(), "Alert", " alert('Please Add Items for Adjustment Entry !');", true); } else { string SqlQuery = " SELECT DISTINCT SITECODE,PRODUCTCODE,ISNULL(SUM(TRANSQTY),0) TRANSQTY FROM AX.ACXINVENTTRANS IT WHERE SITECODE='" + Session["SiteCode"].ToString() + "'" + " AND TRANSLOCATION='" + DDLWarehouseFrom.SelectedValue.ToString() + "' GROUP BY SITECODE,PRODUCTCODE HAVING ISNULL(SUM(TRANSQTY),0)>0 "; CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); DataTable dtPrdStock = obj.GetData(SqlQuery); DataRow[] drrow; for (int i = 0; i < gridStockTransferItems.Rows.Count; i++) { string productNameCode = gridStockTransferItems.Rows[i].Cells[3].Text; string[] str = productNameCode.Split('-'); string ProductCode = str[0].ToString(); drrow = dtPrdStock.Select("PRODUCTCODE='" + ProductCode + "'"); if (drrow.Length == 0) { _value = false; this.LblMessage.Text = "► " + productNameCode + " stock not available. Please remove !"; return(_value); } else { decimal TransQty = Convert.ToDecimal(gridStockTransferItems.Rows[i].Cells[6].Text); decimal stockqty = Convert.ToDecimal(drrow[0]["TRANSQTY"]); if (stockqty < TransQty) { _value = false; this.LblMessage.Text = "► " + productNameCode + " stock not available. Please remove !"; return(_value); } } } _value = true; } return(_value); }
private void ShowReportSummary() { CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); DataTable dtSetData = null; SqlCommand cmd = new SqlCommand(); cmd.CommandTimeout = 0; try { string query = "Select Top 1 NAME from ax.inventsite where SITEID='" + Session["SiteCode"].ToString() + "'"; object obj1 = obj.GetScalarValue(query); string sqlstr = @"Select I.Siteid,I.Name,I.StateCode,from_Date,To_Date,TC.Name as Cat,TS.Name as Subcat,Target_Description,Actual_Incentive, Case when Claim_Type = '0' then 'Sale' else 'Purchase' end as ClaimType 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].[INVENTSITE] I on CM.SITE_CODE = I.Siteid where CM.from_Date >= " + " '" + Convert.ToDateTime(txtFromDate.Text) + "' and CM.TO_DATE <= '" + Convert.ToDateTime(txtToDate.Text) + "' "; // " "; if (ddlSiteId.SelectedIndex != -1) { if (ddlSiteId.SelectedItem.Text != "Select...") { sqlstr += "and CM.SITE_CODE = '" + ddlSiteId.SelectedItem.Value + "' "; } } dtSetData = new DataTable(); dtSetData = obj.GetData(sqlstr); LoadDataInReportViewerDetail(dtSetData); } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } }
protected void FillState() { try { string sqlstr = "select * from ax.ACXSITEMENU where SITE_CODE ='" + Session["SiteCode"].ToString() + "'"; object objcheckSitecode = baseObj.GetScalarValue(sqlstr); DataTable dt = new DataTable(); dt = new DataTable(); if (objcheckSitecode != null) { chkListState.Items.Clear(); string sqlstr11 = "Select Distinct I.StateCode Code,LS.Name from [ax].[INVENTSITE] I left join [ax].[LOGISTICSADDRESSSTATE] LS on LS.STATEID = I.STATECODE where I.STATECODE <>'' "; dt = baseObj.GetData(sqlstr11); chkListState.Items.Add("All..."); for (int i = 0; i < dt.Rows.Count; i++) { chkListState.DataSource = dt; chkListState.DataTextField = "NAME"; chkListState.DataValueField = "Code"; chkListState.DataBind(); } } else { chkListState.Items.Clear(); chkListSite.Items.Clear(); string sqlstr1 = @"Select I.StateCode StateCode,LS.Name as StateName,I.SiteId,I.Name as SiteName from [ax].[INVENTSITE] I left join [ax].[LOGISTICSADDRESSSTATE] LS on LS.STATEID = I.STATECODE where I.SiteId = '" + Session["SiteCode"].ToString() + "'"; dt = baseObj.GetData(sqlstr1); for (int i = 0; i < dt.Rows.Count; i++) { chkListState.DataSource = dt; chkListState.DataTextField = "StateName"; chkListState.DataValueField = "StateCode"; chkListState.DataBind(); chkListSite.DataSource = dt; chkListSite.DataTextField = "SiteName"; chkListSite.DataValueField = "SiteId"; chkListSite.DataBind(); } chkListState.Items[0].Selected = true; chkListSite.Items[0].Selected = true; FillCustomerGroup(); } } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); } }
protected void fillPSR() { string siteid = ""; foreach (System.Web.UI.WebControls.ListItem litem in ddlSiteId.Items) { if (litem.Selected) { if (siteid.Length == 0) { siteid = "'" + litem.Value.ToString() + "'"; } else { siteid += ",'" + litem.Value.ToString() + "'"; } } } if (ddlSiteId.SelectedValue != string.Empty) { DataTable dt = new DataTable(); string query = @"Select PSR_Code +'-'+ PSR_Name as PSRName,PSR_Code from [ax].[ACXPSRMaster] where PSR_Code " + " in (select A.PSRCode from [ax].[ACXPSRBeatMaster] A " + " left Join [ax].[ACXPSRSITELinkingMaster] B on A.PSRCode = B.PSRCode " + " where B.Site_code in (" + siteid + "))"; dt = new DataTable(); drpPSR.Items.Clear(); dt = baseObj.GetData(query); //drpPSR.Items.Add("Select..."); drpPSR.DataSource = dt; drpPSR.DataTextField = "PSRName"; drpPSR.DataValueField = "PSR_Code"; drpPSR.DataBind(); //drpPSR.Items.Clear(); // //baseobj.BindToDropDown(drpPSR, query, "PSRName", "PSR_Code"); } }
protected void txtAmount_TextChanged(object sender, EventArgs e) { lblMessage.Text = ""; if (ddlCompany.SelectedItem.Text != "Select...") { if (txtAmount.Text != "") { if (Convert.ToDecimal(txtAmount.Text) > 0) { DataTable dt = new DataTable(); dt = baseObj.GetData("select * from VW_GetSupplierList WHERE (SITEID = '" + Session["SITECODE"].ToString() + "' or SITEID = '') AND SUPPLIERCODE = '" + ddlCompany.SelectedItem.Value.ToString() + "'"); if (dt.Rows.Count > 0) { Session["SerInvLine"] = null; DataTable dtNew = new DataTable(); string strQuery = "EXEC USP_ACX_GETTAXRATECALCGST_SERVICEINVOICING '" + Session["SITELOCATION"].ToString() + "','" + dt.Rows[0]["STATEID"].ToString() + "','" + Convert.ToDecimal(txtAmount.Text) + "','" + dt.Rows[0]["COMPOSITIONSCHEME"].ToString() + "','" + dt.Rows[0]["GSTINNO"].ToString() + "'"; dtNew = baseObj.GetData(strQuery); if (dtNew.Rows.Count > 0) { char sp = '|'; string[] retmsg = dtNew.Rows[0]["RETMSG"].ToString().Split(sp); if (retmsg[0].ToUpper() == "TRUE") { if (dtNew.Rows[0]["RETMSG"].ToString().IndexOf("FALSE") >= 0) { string message = "alert('" + dtNew.Rows[0]["RETMSG"].ToString().Replace("FALSE|", "") + "');"; ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", message, true); return; } else { Session["SerInvLine"] = dtNew; txtTotTax.Text = Math.Round(Convert.ToDecimal(dtNew.Rows[0]["TOTTAXAMOUNT"].ToString()), 2).ToString(); txtFAmount.Text = Math.Round(Convert.ToDecimal(dtNew.Rows[0]["FINALAMOUNT"].ToString()), 2).ToString(); } } } } } else { string message = "alert('Amount Entered Should be Greater Than Zero...');"; ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", message, true); txtAmount.Text = ""; txtTotTax.Text = ""; txtFAmount.Text = ""; return; } } else { txtTotTax.Text = ""; txtFAmount.Text = ""; } } else { string message = "alert('Please Select Company Before Entering Amount...');"; ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", message, true); txtAmount.Text = ""; txtTotTax.Text = ""; txtFAmount.Text = ""; return; } }
protected void Page_Load(object sender, EventArgs e) { if (Session["USERID"] == null) { Response.Redirect("Login.aspx"); return; } if (!IsPostBack) { //btnShow.Attributes.Add("onclick", "javascript: if (Page_ClientValidate() ){" + btnShow.ClientID + ".disabled=true;}" + ClientScript.GetPostBackEventReference(btnShow, "")); string sqlstr1 = @"select WAREHOUSENAME,WAREHOUSEVALUE from AX.ACXWAREHOUSES"; ddlWarehouse.Items.Clear(); DataTable dt1 = new DataTable(); dt1 = baseObj.GetData(sqlstr1); ddlWarehouse.DataSource = dt1; ddlWarehouse.DataTextField = "WAREHOUSENAME"; ddlWarehouse.DataValueField = "WAREHOUSEVALUE"; ddlWarehouse.DataBind(); baseObj.FillSaleHierarchy(); fillHOS(); if (Convert.ToString(Session["LOGINTYPE"]) == "3") { // DataView DtSaleHierarchy = (DataTable)HttpContext.Current.Session["SaleHierarchy"]; DataTable dt = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE); if (dt.Rows.Count > 0) { var dr_row = dt.AsEnumerable(); var test = (from r in dr_row select r.Field <string>("SALEPOSITION")).First <string>(); //string dr1 = dt.Select("SALEPOSITION").ToString(); if (test == "VP") { chkListHOS.Enabled = false; chkAll.Enabled = false; chkAll.Checked = true; // chkAll_CheckedChanged(null, null); } else if (test == "GM") { chkListHOS.Enabled = false; chkListVP.Enabled = false; chkAll.Enabled = false; chkAll.Checked = true; CheckBox1.Enabled = false; CheckBox1.Checked = true; // chkAll_CheckedChanged(null, null); } else if (test == "DGM") { chkListHOS.Enabled = false; chkListVP.Enabled = false; chkListGM.Enabled = false; chkAll.Enabled = false; chkAll.Checked = true; CheckBox1.Enabled = false; CheckBox1.Checked = true; CheckBox2.Enabled = false; CheckBox2.Checked = true; //chkAll_CheckedChanged(null, null); } else if (test == "RM") { chkListHOS.Enabled = false; chkListVP.Enabled = false; chkListGM.Enabled = false; chkListDGM.Enabled = false; chkAll.Enabled = false; chkAll.Checked = true; CheckBox1.Enabled = false; CheckBox1.Checked = true; CheckBox2.Enabled = false; CheckBox2.Checked = true; CheckBox3.Enabled = false; CheckBox3.Checked = true; //chkAll_CheckedChanged(null, null); } else if (test == "ZM") { chkListHOS.Enabled = false; chkListVP.Enabled = false; chkListGM.Enabled = false; chkListDGM.Enabled = false; chkListRM.Enabled = false; chkAll.Enabled = false; chkAll.Checked = true; CheckBox1.Enabled = false; CheckBox1.Checked = true; CheckBox2.Enabled = false; CheckBox2.Checked = true; CheckBox3.Enabled = false; CheckBox3.Checked = true; CheckBox4.Enabled = false; CheckBox4.Checked = true; // chkAll_CheckedChanged(null, null); } else if (test == "ASM") { chkListHOS.Enabled = false; chkListVP.Enabled = false; chkListGM.Enabled = false; chkListDGM.Enabled = false; chkListRM.Enabled = false; chkListZM.Enabled = false; chkAll.Enabled = false; chkAll.Checked = true; CheckBox1.Enabled = false; CheckBox1.Checked = true; CheckBox2.Enabled = false; CheckBox2.Checked = true; CheckBox3.Enabled = false; CheckBox3.Checked = true; CheckBox4.Enabled = false; CheckBox4.Checked = true; CheckBox5.Enabled = false; CheckBox5.Checked = true; // chkAll_CheckedChanged(null, null); } else if (test == "EXECUTIVE") { chkListHOS.Enabled = false; chkListVP.Enabled = false; chkListGM.Enabled = false; chkListDGM.Enabled = false; chkListRM.Enabled = false; chkListZM.Enabled = false; chkListEXECUTIVE.Enabled = false; chkAll.Enabled = false; chkAll.Checked = true; CheckBox1.Enabled = false; CheckBox1.Checked = true; CheckBox2.Enabled = false; CheckBox2.Checked = true; CheckBox3.Enabled = false; CheckBox3.Checked = true; CheckBox4.Enabled = false; CheckBox4.Checked = true; CheckBox5.Enabled = false; CheckBox5.Checked = true; CheckBox6.Enabled = false; CheckBox6.Checked = true; // chkAll_CheckedChanged(null, null); } ddlCountry_SelectedIndexChanged(null, null); } } if (Convert.ToString(Session["LOGINTYPE"]) == "3") { tclabel.Width = "90%"; Panel1.Visible = true; } else { tclabel.Width = "100%"; Panel1.Visible = false; } } }
protected void btnSearch_Click(object sender, EventArgs e) { try { CreamBell_DMS_WebApps.App_Code.Global baseObj = new CreamBell_DMS_WebApps.App_Code.Global(); if (txtFromDate.Text.Trim().Length > 0) { if (!baseObj.IsDate(txtFromDate.Text)) { string message = "alert('Invalid From Date !!!');"; ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", message, true); txtFromDate.Focus(); return; } } if (txtToDate.Text.Trim().Length > 0) { if (!baseObj.IsDate(txtToDate.Text)) { string message = "alert('Invalid To Date !!!');"; ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", message, true); txtToDate.Focus(); return; } } CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); bool b = ValidateSearch(); GridView1.DataSource = null; GridView2.DataSource = null; GridView2.DataBind(); if (b == true) { DateTime endDate = Convert.ToDateTime(this.txtToDate.Text); //endDate.AddDays(1); //DateTime end = endDate.AddDays(1); string FromDate = Convert.ToDateTime(txtFromDate.Text).ToString("dd-MMM-yyyy"); string ToDate = endDate.ToString("dd-MMM-yyyy"); //string query = "Select A.PURCH_RETURNNO as INVOICE_NO , A.PURCH_RETURNDATE as INVOIC_DATE, A.Purch_recieptno as SO_NO,case when CONVERT(VARCHAR(10),PURCH_RECIEPTDATE , 103)= '01/01/1900' then '' else CONVERT(VARCHAR(10),PURCH_RECIEPTDATE , 103) end AS SO_DATE, ('[' + A.SITE_CODE +']' + ' ' + B.NAME) as CUSTOMER , A.Return_docvalue as INVOICE_VALUE" + // " from ax.[ACXPURCHRETURNHEADER] A INNER JOIN ax.inventsite B ON A.SITE_CODE=B.SITEID" + // " where A.SITE_CODE= '" + Session["SiteCode"].ToString() + "' and PURCH_RETURNDATE>='" + FromDate + "' and PURCH_RETURNDATE<='" + ToDate + "'"; string query = "Select A.PURCH_RETURNNO as INVOICE_NO , A.PURCH_RETURNDATE as INVOIC_DATE, A.Purch_recieptno as SO_NO,case when CONVERT(VARCHAR(10),PURCH_RECIEPTDATE , 103)= '01/01/1900' then '' else CONVERT(VARCHAR(10),PURCH_RECIEPTDATE , 103) end AS SO_DATE, ('[' + A.SITE_CODE +']' + ' ' + B.NAME) as CUSTOMER , A.Return_docvalue as INVOICE_VALUE" + " from ax.[ACXPURCHRETURNHEADER] A INNER JOIN ax.inventsite B ON A.SITE_CODE=B.SITEID" + " where A.SITE_CODE IN (" + ucRoleFilters.GetCommaSepartedSiteId() + ") and PURCH_RETURNDATE>='" + FromDate + "' and PURCH_RETURNDATE<='" + ToDate + "'"; //if (txtInvoiceNoStart.Text != string.Empty && txtInvoiceNoEnd.Text != string.Empty) //{ // try // { // string[] strInvoicefromsplit = txtInvoiceNoStart.Text.Split('-'); // string[] strInvoiceTosplit = txtInvoiceNoEnd.Text.Split('-'); // query = query + "and cast(Right(A.invoice_no,6) as int) between " + strInvoicefromsplit[1] + " and " + strInvoiceTosplit[1] + " "; // } // catch // { // ScriptManager.RegisterStartupScript(this, this.GetType(), "Alert", " alert('Invoice number is not in correct format !! ');", true); // GridView1.DataSource = null; // GridView1.DataBind(); // return; // } // // //} //if (drpPSR.SelectedItem.Text != string.Empty && drpPSR.SelectedItem.Text != "Select...") //{ // query = query + "and B.psr_code='" + drpPSR.SelectedItem.Value + "' "; // // if (drpBeat.SelectedItem.Text != string.Empty && drpBeat.SelectedItem.Text != "Select...") // { // query = query + "and B.psr_beat='" + drpBeat.SelectedItem.Value + "' "; // // if (drpCustomer.SelectedItem.Text != string.Empty && drpCustomer.Text != "Select...") // { // query = query + "and A.CUSTOMER_CODE='" + drpCustomer.SelectedItem.Value + "' "; // } // } //} if (ddlSearchNew.Text == "Sales Invoice No" && txtSearch.Text != "") { query = query + "AND A.PURCH_RETURNNO like '%" + txtSearch.Text.Trim().ToString() + "%'"; } //if (ddlSearch.Text == "Customer") //{ // query = query + " and (A.CUSTOMER_CODE like '%" + txtSearch.Text.Trim().ToString() + "%' or B.CUSTOMER_NAME like '%" + txtSearch.Text.Trim().ToString() + "%') "; //} query = query + " ORDER BY A.PURCH_RETURNDATE DESC, A.PURCH_RETURNNO DESC"; DataTable dtFilter = obj.GetData(query); if (dtFilter.Rows.Count > 0) { GridView1.DataSource = dtFilter; GridView1.DataBind(); CheckBox chk = (CheckBox)GridView1.HeaderRow.FindControl("checkAll"); chk.Checked = true; foreach (GridViewRow grv in GridView1.Rows) { CheckBox chkAll = (CheckBox)grv.Cells[0].FindControl("chklist"); chkAll.Checked = true; } // uppanel.Update(); } else { ScriptManager.RegisterStartupScript(this, this.GetType(), "Alert", " alert('No Data Exist !! ');", true); GridView1.DataSource = null; GridView1.DataBind(); //GridDetail(); txtSearch.Text = string.Empty; } } } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); } }
private void LoadDataInReportViewer(DataTable dtSetData, DataTable dtSetDataClaim) { try { if (dtSetData.Rows.Count > 0) { DataTable DataSetParameter = new DataTable(); DataSetParameter.Columns.Add("SiteName"); DataSetParameter.Columns.Add("City"); DataSetParameter.Columns.Add("State"); DataSetParameter.Columns.Add("Month"); DataSetParameter.Columns.Add("VRSName"); DataSetParameter.Rows.Add(); if (ddlSiteId.SelectedIndex > 0) { DataSetParameter.Rows[0]["SiteName"] = ddlSiteId.SelectedItem.Text; } else { DataSetParameter.Rows[0]["SiteName"] = ""; } if (ddlState.SelectedIndex > 0) { DataTable dtCity = new DataTable(); dtCity = baseObj.GetData("select AcxCity from ax.inventsite where siteid='" + ddlSiteId.SelectedItem.Value + "'"); if (dtCity.Rows.Count > 0) { DataSetParameter.Rows[0]["City"] = dtCity.Rows[0]["AcxCity"]; } DataSetParameter.Rows[0]["State"] = ddlState.SelectedItem.Text; } else { DataSetParameter.Rows[0]["City"] = ""; DataSetParameter.Rows[0]["State"] = ""; } DataSetParameter.Rows[0]["Month"] = txtFromDate.Text; if (ddlVRS.SelectedIndex > 0) { DataSetParameter.Rows[0]["VRSName"] = ddlVRS.SelectedItem.Text; } else { DataSetParameter.Rows[0]["VRSName"] = ""; } //DataSetParameter.Rows[0]["VRSName"] = ddlVRS.SelectedItem.Text.ToString() +" "+ ddlVRS.SelectedValue.ToString(); ReportViewer1.LocalReport.ReportPath = Server.MapPath("Reports\\VRSMonthlyExpenseMonitoringSummary.rdl"); ReportViewer1.AsyncRendering = true; ReportParameter FromDate = new ReportParameter(); FromDate.Name = "FromDate"; FromDate.Values.Add(txtFromDate.Text); ReportViewer1.ProcessingMode = ProcessingMode.Local; ReportDataSource RDS1 = new ReportDataSource("DSetData", dtSetData); ReportViewer1.LocalReport.DataSources.Clear(); ReportViewer1.LocalReport.DataSources.Add(RDS1); ReportDataSource RDS2 = new ReportDataSource("DSetDataClaim", dtSetDataClaim); ReportViewer1.LocalReport.DataSources.Add(RDS2); ReportDataSource RDS3 = new ReportDataSource("DataSetHeader", DataSetParameter); ReportViewer1.LocalReport.DataSources.Add(RDS3); this.ReportViewer1.LocalReport.Refresh(); ReportViewer1.Visible = true; LblMessage.Text = String.Empty; } else { LblMessage.Text = "No Records Exists !!"; ReportViewer1.Visible = false; } } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } }
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; try { var siteCodes = ucRoleFilters.GetCommaSepartedSiteId(); string query = " Select NAME from ax.inventsite where siteid IN (" + siteCodes + ")"; //string query = " Select NAME from ax.inventsite where siteid='" + Session["SiteCode"].ToString() + "'"; dtSetHeader = new DataTable(); dtSetHeader = obj.GetData(query); dtSetHeader.Columns.Add("FromDate"); dtSetHeader.Columns.Add("ToDate"); dtSetHeader.Rows.Add(query); dtSetHeader.Rows[0]["FromDate"] = txtFromDate.Text; dtSetHeader.Rows[0]["ToDate"] = txtToDate.Text; string CustomerGroup = string.Empty; string Customer = string.Empty; string ProductGroup = string.Empty; string ProdSubCategory = string.Empty; string Product = string.Empty; if (DDLCustGroupNew.SelectedIndex > 0) { CustomerGroup = DDLCustGroupNew.SelectedValue.ToString(); } else { CustomerGroup = ""; } if (DDLCustomersNew.SelectedIndex > 0) { Customer = DDLCustomersNew.SelectedValue.ToString(); } else { Customer = ""; } if (DDLProductGroupNew.SelectedIndex > 0) { ProductGroup = DDLProductGroupNew.SelectedValue.ToString(); } else { ProductGroup = ""; } if (DDLSubCategoryNew.SelectedIndex > 0) { ProdSubCategory = DDLSubCategoryNew.SelectedValue.ToString(); } else { ProdSubCategory = ""; } if (DDLProductNew.SelectedIndex > 0) { Product = DDLProductNew.SelectedValue.ToString(); } else { Product = ""; } //FilterQuery = "EXEC SP_SALEREGISTER '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "','" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "','" + CustomerGroup + "','" + Customer + "','" + ProductGroup + "','" + ProdSubCategory + "','" + Product + "'"; FilterQuery = "EXEC SP_SALEREGISTER '" + siteCodes + "','" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "','" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "','" + CustomerGroup + "','" + Customer + "','" + ProductGroup + "','" + ProdSubCategory + "','" + Product + "'"; dtSetData = new DataTable(); dtSetData = obj.GetData(FilterQuery); LoadDataInReportViewer(dtSetHeader, dtSetData); //#region Filter By Both Customer and Product Wise //FilterQuery = "EXEC SP_SALEREGISTER '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text) + "','" + Convert.ToDateTime(txtToDate.Text) + "'"; //if (DDLCustGroup.Text == "ALL" && DDLProductGroup.Text == "ALL") //{ // dtSetData = new DataTable(); // dtSetData = obj.GetData(FilterQuery); //} //if (DDLCustGroup.Text != "ALL" && DDLCustomers.Text == "-Select-" && DDLProductGroup.Text == "ALL") //{ // FilterQuery = "EXEC SP_SALEREGISTER '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text) + "','" + Convert.ToDateTime(txtToDate.Text) + "','" + DDLCustGroup.SelectedValue + "'"; // dtSetData = new DataTable(); // dtSetData = obj.GetData(FilterQuery); //} //if (DDLCustGroup.Text != "ALL" && DDLCustomers.Text != "-Select-" && DDLProductGroup.Text == "ALL") //{ // FilterQuery = "EXEC SP_SALEREGISTER '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text) + "','" + Convert.ToDateTime(txtToDate.Text) + "','" + DDLCustGroup.SelectedValue + "','" + DDLCustomers.SelectedValue + "'"; // dtSetData = new DataTable(); // dtSetData = obj.GetData(FilterQuery); //} //if (DDLCustGroup.Text != "ALL" && DDLCustomers.Text == "-Select-" && DDLProductGroup.Text != "ALL" && DDLSubCategory.Text != "-Select-" && DDLProduct.Text != "-Select") //{ // FilterQuery = "EXEC SP_SALEREGISTER '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text) + "','" + Convert.ToDateTime(txtToDate.Text) + "','" + DDLCustGroup.SelectedValue + "',@productgroup='" + DDLProductGroup.SelectedValue + "',@subcategory='" + DDLSubCategory.SelectedValue + "',@productname='" + DDLProduct.SelectedValue + "'"; // dtSetData = new DataTable(); // dtSetData = obj.GetData(FilterQuery); //} //else if (DDLCustGroup.Text != "ALL" && DDLCustomers.Text == "-Select-" && DDLProductGroup.Text != "ALL" && DDLSubCategory.Text != "-Select-") //{ // FilterQuery = "EXEC SP_SALEREGISTER '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text) + "','" + Convert.ToDateTime(txtToDate.Text) + "','" + DDLCustGroup.SelectedValue + "',@productgroup='" + DDLProductGroup.SelectedValue + "',@subcategory='" + DDLSubCategory.SelectedValue + "'"; // dtSetData = new DataTable(); // dtSetData = obj.GetData(FilterQuery); //} //else if (DDLCustGroup.Text != "ALL" && DDLCustomers.Text == "-Select-" && DDLProductGroup.Text != "ALL") //{ // FilterQuery = "EXEC SP_SALEREGISTER '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text) + "','" + Convert.ToDateTime(txtToDate.Text) + "','" + DDLCustGroup.SelectedValue + "',@productgroup='" + DDLProductGroup.SelectedValue + "'"; // dtSetData = new DataTable(); // dtSetData = obj.GetData(FilterQuery); //} //else if (DDLCustGroup.Text != "ALL" && DDLCustomers.Text != "-Select-" && DDLProductGroup.Text != "ALL") //{ // FilterQuery = "EXEC SP_SALEREGISTER '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text) + "','" + Convert.ToDateTime(txtToDate.Text) + "','" + DDLCustGroup.SelectedValue + "','" + DDLCustomers.SelectedValue + "','" + DDLProductGroup.SelectedValue + "'"; // dtSetData = new DataTable(); // dtSetData = obj.GetData(FilterQuery); //} //if (DDLCustGroup.Text != "ALL" && DDLCustomers.Text != "-Select-" && DDLProductGroup.Text != "ALL" && DDLSubCategory.Text != "-Select-") //{ // FilterQuery = "EXEC SP_SALEREGISTER '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text) + "','" + Convert.ToDateTime(txtToDate.Text) + "'"; // dtSetData = new DataTable(); // dtSetData = obj.GetData(FilterQuery); //} //if (DDLCustGroup.Text != "ALL" && DDLCustomers.Text != "-Select-" && DDLProductGroup.Text != "ALL" && DDLSubCategory.Text != "-Select-" && DDLProduct.Text != "-Select") //{ // FilterQuery = "EXEC SP_SALEREGISTER '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text) + "','" + Convert.ToDateTime(txtToDate.Text) + "'"; // dtSetData = new DataTable(); // dtSetData = obj.GetData(FilterQuery); //} //if (DDLCustGroup.Text == "ALL" && DDLProductGroup.Text != "ALL" && DDLSubCategory.Text == "-Select-") //{ // FilterQuery = "EXEC SP_SALEREGISTER '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text) + "','" + Convert.ToDateTime(txtToDate.Text) + "'"; // dtSetData = new DataTable(); // dtSetData = obj.GetData(FilterQuery); //} //if (DDLCustGroup.Text == "ALL" && DDLProductGroup.Text != "ALL" && DDLSubCategory.Text != "-Select-" && DDLProduct.Text == "-Select-") //{ // FilterQuery = "EXEC SP_SALEREGISTER '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text) + "','" + Convert.ToDateTime(txtToDate.Text) + "'"; // dtSetData = new DataTable(); // dtSetData = obj.GetData(FilterQuery); //} //if (DDLCustGroup.Text == "ALL" && DDLProductGroup.Text != "ALL" && DDLSubCategory.Text != "-Select-" && DDLProduct.Text != "-Select-") //{ // FilterQuery = "EXEC SP_SALEREGISTER '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text) + "','" + Convert.ToDateTime(txtToDate.Text) + "'"; // dtSetData = new DataTable(); // dtSetData = obj.GetData(FilterQuery); //} //#endregion } catch (Exception ex) { LblMessage.Text = ex.Message.ToString(); ErrorSignal.FromCurrentContext().Raise(ex); } }
protected void Button2_Click(object sender, EventArgs e) { try { string multipleInvoice = string.Empty; foreach (GridViewRow grv in GridView1.Rows) { CheckBox chklist1 = (CheckBox)grv.Cells[0].FindControl("chklist"); LinkButton lnkBtn = (LinkButton)grv.Cells[0].FindControl("lnkbtn"); if (chklist1.Checked) { // ShowReportSaleInvoice(string.Empty, chklist1.Text); if (multipleInvoice == string.Empty) { multipleInvoice = "'" + lnkBtn.Text + "'"; } else { multipleInvoice += ",'" + lnkBtn.Text + "'"; } } } if (multipleInvoice == "") { LblMessage.Text = "Please select Invoice No.. !"; //UpdatePanel1.Update(); // this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Please select Invoice No.. !');", true); //string message = " alert('Please select Invoice No.. !');"; //ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", message, true); return; } //================== //List<string> ilist = new List<string>(); //List<string> litem = new List<string>(); //string queryHeader = "Acx_SaleInvoiceHeaderReport"; //string queryLine = "Acx_SaleInvoiceLineReport"; string queryHeader = "USP_GETDEBITNOTEHEADER" + "'" + multipleInvoice + "','" + Session["SiteCode"].ToString() + "'"; string queryLine = "USP_GETDEBITNOTELINE" + "'" + multipleInvoice + "','" + Session["SiteCode"].ToString() + "'"; //string queryHeader = " Select SHD.INVOICE_NO, IST.ACXADDRESS1 +' '+ACXADDRESS2 AS SITEADDRESS, IST.AcxCity,IST.ACXzipcode,IST.STATECODE AS SITESTATE, IST.ACXVAT AS SITEVAT," + // " IST.ACXMOBILE AS SITEMOBILE,IST.ACXTELEPHONE AS SitePhoneNo, CONVERT(nvarchar(15), SHD.INVOIC_DATE,105) as INVOIC_DATE, SHD.SITEID, " + // " USM.User_Name, USM.State, SHD.CUSTGROUP_CODE,SHD.CUSTOMER_CODE, CUS.CUSTOMER_NAME, CUS.ADDRESS1,CUS.ADDRESS2,CUS.CITY, " + // " CUS.AREA,CUS.DISTRICT, CUS.STATE, CUS.MOBILE_NO, CUS.PHONE_NO,CUS.VAT,SHD.SO_NO, CONVERT(nvarchar(15),SHD.SO_DATE,105) as SO_DATE ," + // " SHD.LOADSHEET_NO, CONVERT(nvarchar(15),SHD.LOADSHEET_DATE,105) as LOADSHEET_DATE ,SHD.TRANSPORTER_CODE, SHD.VEHICAL_NO, " + // " SHD.DRIVER_CODE, SHD.DRIVER_MOBILENO, SHD.INVOICE_VALUE, SHD.LOADSHEETQTY, " + // " CASE WHEN CUS.VAT='' THEN 'RETAIL INVOICE' ELSE 'TAX INVOICE' END AS REPORTTYPE , Remark" + // " from ax.ACXSALEINVOICEHEADER SHD " + // " INNER JOIN ax.ACXCUSTMASTER CUS ON SHD.CUSTOMER_CODE=CUS.CUSTOMER_CODE " + // " INNER JOIN ax.ACXUSERMASTER USM ON SHD.SITEID= USM.SITE_CODE " + // " INNER JOIN AX.INVENTSITE IST ON IST.SITEID = SHD.SITEID " + // "where INVOICE_NO in (" + multipleInvoice + ") and SHD.SITEID='" + Session["SiteCode"].ToString() + "' order by SHD.INVOICE_NO "; // string queryLine = @"Select ROW_NUMBER() over (ORDER BY SINVL.Amount Desc,PROD.PRODUCT_SUBCATEGORY, PRODUCT_NAME) AS SRNO,case when amount<>0 then 'A' else 'B' end as SortFilter // ,PROD.PRODUCT_SUBCATEGORY ,SINVL.Invoice_No, SINVL.PRODUCT_CODE, // PRODUCT_NAME as PRODUCT_NAME, // CAST(PROD.PRODUCT_PACKSIZE as decimal(9,2)) as PRODUCT_PACKSIZE , // CAST(SINVL.BOXPCS as decimal(9,2)) as BOX,CAST(SINVL.BOX as decimal(9,2)) as BoxConv,CAST(SINVL.MRP as decimal(9,2)) as MRP, // CAST((SINVL.BOX * SINVL.MRP) as decimal(9,2)) as MRPValue, // CAST(SINVL.LTR as decimal(9,2)) as LTR, // CAST(SINVL.TAX_CODE as decimal(9,2)) as TAX_CODE, CAST(SINVL.CRATES as decimal(9,2)) as CRATES, // CAST(SINVL.AMOUNT as decimal(9,2)) as AMOUNT, CAST(SINVL.TAX_AMOUNT as decimal(9,2)) as TAX_AMOUNT, // CAST(SINVL.LINEAMOUNT as decimal(9,2)) as LINEAMOUNT, CAST(SINVL.ADDTAX_AMOUNT as decimal(9,2)) as ADDTAX_AMOUNT, // CAST(SINVL.ADDTAX_CODE as decimal(9,2)) as ADDTAX_CODE, CAST(SINVL.DISC_AMOUNT as decimal(9,2)) as DISC_AMOUNT // ,SINVL.SEC_DISC_AMOUNT,isnull(SINVL.PEVAlue,0) as PEValue,isnull(TDValue,0) as TDValue , // CAST(SINVL.AMOUNT as Decimal(9,2)) - // CAST(SINVL.TAX_AMOUNT as Decimal(9,2)) - // CAST(SINVL.ADDTAX_AMOUNT as decimal(9,2)) + isnull(TDValue,0) - // Isnull(SINVL.PEVAlue,0) + CAST(SINVL.DISC_AMOUNT as decimal(9,2)) + SINVL.SEC_DISC_AMOUNT as basic, // Case when SINVL.BOX = 0 then 0.00 else ( // Cast((CAST(SINVL.AMOUNT as Decimal(9,2)) - // CAST(SINVL.TAX_AMOUNT as Decimal(9,2)) - // CAST(SINVL.ADDTAX_AMOUNT as decimal(9,2)) + isnull(TDValue,0) - // Isnull(SINVL.PEVAlue,0) + CAST(SINVL.DISC_AMOUNT as decimal(9,2)) + SINVL.SEC_DISC_AMOUNT) / CAST(SINVL.BOX as decimal(9,2)) as decimal(9,2)) // ) End as RATE, // // CAST(SINVL.AMOUNT as Decimal(9,2)) - CAST(SINVL.TAX_AMOUNT as Decimal(9,2)) - CAST(SINVL.ADDTAX_AMOUNT as decimal(9,2)) as TaxableAmt // from ax.ACXSALEINVOICELINE SINVL // INNER JOIN ax.inventtable PROD ON SINVL.PRODUCT_CODE=PROD.ITEMID " + // " where INVOICE_NO in (" + multipleInvoice + ") and SINVL.SITEID='" + Session["SiteCode"].ToString() + "' Order By INVOICE_NO ,SortFilter,Product_SubCategory "; DataTable dtMainHeader1 = new DataTable(); dtMainHeader1 = obj.GetData(queryHeader); DataTable dtLine1 = new DataTable(); dtLine1 = obj.GetData(queryLine); string[] strSavePDFID = new string[dtMainHeader1.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(dtMainHeader1, dtLine1, 0, 0, strSavePDFID); // for (int i = 0; i < dtMainHeader1.Rows.Count; i++) { SaveInvoicePDf(dtMainHeader1, dtLine1, 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(); } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); } }
protected void btnsave_Click(object sender, EventArgs e) { try { SqlConnection con = baseObj.GetConnection(); SqlTransaction tran = null; SqlCommand cmd = new SqlCommand(); try { DataTable dt = baseObj.GetNumSequenceNew(11, Session["SiteCode"].ToString(), Session["DATAAREAID"].ToString()); //st.Substring(st.Length - 6) + System.DateTime.Now.ToString("yyMMddhhmmss"); string Doc_NO = string.Empty; string NUMSEQ = string.Empty; if (dt != null) { Doc_NO = dt.Rows[0][0].ToString(); NUMSEQ = dt.Rows[0][1].ToString(); } else { return; } if (con.State.ToString() == "Closed") { con.Open(); } tran = con.BeginTransaction(); cmd.Transaction = tran; Boolean FoundAlready, FoundSuccess; FoundAlready = false; FoundSuccess = false; //foreach (GridViewRow grv in grvDetail.Rows) for (int i = 0; i < grvDetail.Rows.Count; i++) { string OBJECT = string.Empty; if (grvDetail.Rows[i].Cells[6].Text.ToString() == "Site") { OBJECT = "1"; } else if (grvDetail.Rows[i].Cells[6].Text.ToString() == "PSR") { OBJECT = "0"; } else { OBJECT = "2"; } string OBJECT_CODE = grvDetail.Rows[i].Cells[7].Text.ToString().Replace(" ", ""); string OBJECT_SUBCODE = grvDetail.Rows[i].Cells[8].Text.ToString().Replace(" ", ""); string TARGET_CODE = grvDetail.Rows[i].Cells[1].Text.ToString().Replace(" ", ""); string TARGET_DESCRIPTION = grvDetail.Rows[i].Cells[2].Text.ToString().Replace(" ", ""); string FROM_DATE = Convert.ToDateTime(grvDetail.Rows[i].Cells[4].Text).ToString("yyyy-MM-dd"); string TO_DATE = Convert.ToDateTime(grvDetail.Rows[i].Cells[5].Text).ToString("yyyy-MM-dd"); string TARGET = Convert.ToDouble(grvDetail.Rows[i].Cells[18].Text.ToString().Replace(" ", "")).ToString(); string ACHIVEMENT = Convert.ToDouble(grvDetail.Rows[i].Cells[20].Text.ToString().Replace(" ", "")).ToString(); string TARGET_INCENTIVE = Convert.ToDouble(grvDetail.Rows[i].Cells[19].Text.ToString().Replace(" ", "")).ToString(); string ACTUAL_INCENTIVE = Convert.ToDouble(grvDetail.Rows[i].Cells[21].Text.ToString().Replace(" ", "")).ToString(); string CLAIM_CATEGORY = grvDetail.Rows[i].Cells[14].Text.ToString().Replace(" ", ""); string CLAIM_SUBCATEGORY = grvDetail.Rows[i].Cells[16].Text.ToString().Replace(" ", ""); string CLAIM_TYPE = drptargetType.SelectedValue.ToString(); string STATUS = "0"; string MODIFIEDDATETIME = string.Empty; string CREATEDDATETIME = string.Empty; string TARGET_GROUP = grvDetail.Rows[i].Cells[13].Text.ToString().Replace(" ", ""); string CALCULATION_PATTERN = grvDetail.Rows[i].Cells[11].Text.ToString().Replace(" ", ""); string sqlstr = @"SELECT * FROM AX.ACXCLAIMMASTER where SITE_CODE='" + Session["SiteCode"].ToString() + "' and FROM_DATE='" + FROM_DATE + "' "; sqlstr += "and TO_DATE='" + TO_DATE + "' and OBJECT_CODE='" + OBJECT_CODE + "' AND OBJECT='" + OBJECT + "' and OBJECT_SUBCODE='" + OBJECT_SUBCODE + "' and TARGET_CODE='" + TARGET_CODE + "' "; sqlstr += "and TARGET=" + TARGET + " and CLAIM_CATEGORY='" + CLAIM_CATEGORY + "' and CLAIM_SUBCATEGORY='" + CLAIM_SUBCATEGORY + "' and DATAAREAID='" + Session["DATAAREAID"] + "' and TARGET_GROUP='" + TARGET_GROUP + "'"; dt = baseObj.GetData(sqlstr); if (dt.Rows.Count == 0) { #region this for creating a single row of fix incentive //for (int j = i + 1; j < grvDetail.Rows.Count; j++) //{ // string NEXT_OBJECT = string.Empty; // if (grvDetail.Rows[j].Cells[6].Text.ToString() == "Site") // NEXT_OBJECT = "1"; // else if (grvDetail.Rows[j].Cells[6].Text.ToString() == "PSR") // NEXT_OBJECT = "0"; // else // NEXT_OBJECT = "2"; // string NEXT_OBJECT_CODE = grvDetail.Rows[j].Cells[7].Text.ToString().Replace(" ", ""); // string NEXT_OBJECT_SUBCODE = grvDetail.Rows[j].Cells[9].Text.ToString().Replace(" ", ""); // string NEXT_TARGET_CODE = grvDetail.Rows[j].Cells[1].Text.ToString().Replace(" ", ""); // string NEXT_FROM_DATE = Convert.ToDateTime(grvDetail.Rows[j].Cells[4].Text).ToString("yyyy-MM-dd"); // string NEXT_TO_DATE = Convert.ToDateTime(grvDetail.Rows[j].Cells[5].Text).ToString("yyyy-MM-dd"); // string NEXT_ACTUAL_INCENTIVE = Convert.ToDouble(grvDetail.Rows[j].Cells[21].Text.ToString().Replace(" ", "")).ToString(); // string NEXT_CLAIM_CATEGORY = grvDetail.Rows[j].Cells[14].Text.ToString().Replace(" ", ""); // string NEXT_CLAIM_SUBCATEGORY = grvDetail.Rows[j].Cells[16].Text.ToString().Replace(" ", ""); // string NEXT_TARGET_GROUP = grvDetail.Rows[j].Cells[13].Text.ToString().Replace(" ", ""); ; // if (FROM_DATE == NEXT_FROM_DATE && TO_DATE == NEXT_TO_DATE && OBJECT_CODE == NEXT_OBJECT_CODE && OBJECT == NEXT_OBJECT && TARGET_CODE == NEXT_TARGET_CODE && CLAIM_CATEGORY == NEXT_CLAIM_CATEGORY && CLAIM_SUBCATEGORY == NEXT_CLAIM_SUBCATEGORY && TARGET_GROUP == NEXT_TARGET_GROUP && OBJECT_SUBCODE == NEXT_OBJECT_SUBCODE) // { // ACTUAL_INCENTIVE += NEXT_ACTUAL_INCENTIVE; // } // else // { break; } //} #endregion if (Convert.ToDouble(ACTUAL_INCENTIVE) > 0) { cmd.Connection = con; cmd.Parameters.Clear(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "ACXINSERTCLAIMMASTER"; cmd.Parameters.Add("@DATAAREAID", SqlDbType.NVarChar).Value = Session["DATAAREAID"]; cmd.Parameters.Add("@DOCUMENT_CODE", SqlDbType.NVarChar).Value = Doc_NO; cmd.Parameters.Add("@SITE_CODE", SqlDbType.NVarChar).Value = Session["SiteCode"].ToString(); cmd.Parameters.Add("@OBJECT", SqlDbType.NVarChar).Value = OBJECT; cmd.Parameters.Add("@OBJECT_CODE", SqlDbType.NVarChar).Value = OBJECT_CODE; cmd.Parameters.Add("@OBJECT_SUBCODE", SqlDbType.NVarChar).Value = OBJECT_SUBCODE; cmd.Parameters.Add("@TARGET_CODE", SqlDbType.NVarChar).Value = TARGET_CODE; cmd.Parameters.Add("@TARGET_DESCRIPTION", SqlDbType.NVarChar).Value = TARGET_DESCRIPTION; cmd.Parameters.Add("@FROM_DATE", SqlDbType.SmallDateTime).Value = FROM_DATE; cmd.Parameters.Add("@TO_DATE", SqlDbType.SmallDateTime).Value = TO_DATE; cmd.Parameters.Add("@TARGET", SqlDbType.Decimal).Value = TARGET; cmd.Parameters.Add("@ACHIVEMENT", SqlDbType.Decimal).Value = ACHIVEMENT; cmd.Parameters.Add("@TARGET_INCENTIVE", SqlDbType.Decimal).Value = TARGET_INCENTIVE; cmd.Parameters.Add("@ACTUAL_INCENTIVE", SqlDbType.Decimal).Value = ACTUAL_INCENTIVE; cmd.Parameters.Add("@CLAIM_CATEGORY", SqlDbType.NVarChar).Value = CLAIM_CATEGORY; cmd.Parameters.Add("@CLAIM_SUBCATEGORY", SqlDbType.NVarChar).Value = CLAIM_SUBCATEGORY; cmd.Parameters.Add("@CLAIM_TYPE", SqlDbType.NVarChar).Value = CLAIM_TYPE; cmd.Parameters.Add("@STATUS", SqlDbType.Int).Value = STATUS; cmd.Parameters.Add("@NumSeq", SqlDbType.BigInt).Value = NUMSEQ; cmd.Parameters.Add("@TARGET_GROUP", SqlDbType.NVarChar).Value = TARGET_GROUP; cmd.Parameters.Add("@CALCULATION_PATTERN", SqlDbType.NVarChar).Value = CALCULATION_PATTERN; cmd.Parameters.Add("CLAIM_MONTH", SqlDbType.NVarChar).Value = null; cmd.Parameters.Add("@BU_CODE", SqlDbType.NVarChar).Value = ddlBusinessUnit.SelectedValue; cmd.ExecuteNonQuery(); // sqlstr = @"INSERT INTO [ax].[ACXCLAIMMASTER] ([DATAAREAID],[RECID],[DOCUMENT_CODE],[DOCUMENT_DATE],[SITE_CODE],[OBJECT],[OBJECT_CODE],[OBJECT_SUBCODE],[TARGET_CODE] // ,[TARGET_DESCRIPTION],[FROM_DATE],[TO_DATE],[TARGET],[ACHIVEMENT],[TARGET_INCENTIVE],[ACTUAL_INCENTIVE],[CLAIM_CATEGORY] // ,[CLAIM_SUBCATEGORY],[CLAIM_TYPE],[STATUS],[MODIFIEDDATETIME],[CREATEDDATETIME],[NumSeq],TARGET_GROUP,CALCULATION_PATTERN) // VALUES ( '" + Session["DATAAREAID"] + "','1','" + Doc_NO + "',getdate(),'" + Session["SiteCode"].ToString() + "'," + OBJECT + ",'" + OBJECT_CODE + "', " + // " '" + OBJECT_SUBCODE + "', '" + TARGET_CODE + "','" + TARGET_DESCRIPTION + "','" + FROM_DATE + "','" + TO_DATE + "'," + TARGET + "," + ACHIVEMENT + ", " + // " " + TARGET_INCENTIVE + "," + ACTUAL_INCENTIVE + ",'" + CLAIM_CATEGORY + "','" + CLAIM_SUBCATEGORY + "','" + CLAIM_TYPE + "','" + STATUS + "','" + MODIFIEDDATETIME + "'," + // " '" + CREATEDDATETIME + "','" + NUMSEQ + "','" + TARGET_GROUP + "','" + CALCULATION_PATTERN + "')"; // cmd.Connection = con; // cmd.CommandText = sqlstr; // cmd.ExecuteNonQuery(); grvDetail.Rows[i].Cells[22].Text = "Success"; FoundSuccess = true; } } else { FoundAlready = true; grvDetail.Rows[i].Cells[22].Text = "Already"; } } tran.Commit(); if (FoundAlready == true && FoundSuccess == true) { ScriptManager.RegisterStartupScript(this, typeof(Page), "Information", "alert('Some data already saved. Except that saved successfully.');", true); } else if (FoundAlready) { ScriptManager.RegisterStartupScript(this, typeof(Page), "Information", "alert('Data already saved.');", true); } else { ScriptManager.RegisterStartupScript(this, typeof(Page), "Information", "alert('Data saved successfully.');", true); } grvDetail.DataSource = null; grvDetail.Visible = false; // grvDetail.Columns[22].Visible = true; } catch (Exception ex) { tran.Rollback(); lblError.Text = ex.Message; ErrorSignal.FromCurrentContext().Raise(ex); } finally { if (con.State.ToString() == "Open") { con.Close(); } } } catch (Exception ex) { lblError.Text = ex.Message; ErrorSignal.FromCurrentContext().Raise(ex); } }
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(); } }