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();
}
}