private void ShowReport()
{
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
string FilterQuery = string.Empty;
//DataTable dtSetHeader = null;
//DataTable dtSetData = null;
//DataTable dtTotalInvoiceNo = null;
try
{
//string query = "Select NAME from ax.inventsite where SITEID='" + Session["SiteCode"].ToString() + "'";
DataTable dtSetHeader = new DataTable();
DataColumn dc = new DataColumn(SessionKeys.NAME);
dtSetHeader.Columns.Add(dc);
dtSetHeader.Rows.Add(Session[SessionKeys.NAME].ToString());
//dtSetHeader = obj.GetData(query);
string CustomerGroup = string.Empty;
string Customer = string.Empty;
string BU = string.Empty;
if (DDLCustGroup.SelectedIndex > 0)
{
CustomerGroup = DDLCustGroup.SelectedValue.ToString();
}
else
{
CustomerGroup = "";
}
if (DDLCustomers.SelectedIndex > 0)
{
Customer = DDLCustomers.SelectedValue.ToString();
}
else
{
Customer = "";
}
if (DDLBusinessUnit.SelectedIndex >= 1)
{
BU = DDLBusinessUnit.SelectedItem.Value.ToString();
}
else
{
BU = "";
}
//FilterQuery = "EXEC SP_PARTYWISESALESUMMARY '" + Session["SiteCode"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "','" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "','" + CustomerGroup + "','" + Customer + "','" + BU + "'";
FilterQuery = "[SP_PARTYWISESALESUMMARY_TI_Version]"; //"SP_PARTYWISESALESUMMARY";
//+ Session["SiteCode"].ToString() + "','"
//+ Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "','"
//+ Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "','"
//+ CustomerGroup + "','"
//+ Customer + "','"
//+ BU + "'";
//FilterQuery = " SELECT SITEID, INVOICE_NO, SIW.CUSTOMER_NAME,C.CUST_GROUP,"
// + " isnull(BOXQty,'0') as Box, isnull(PCSQTY,'0') as PCS, isnull(BOXPCS,'0') as [TotalBoxPCS], BOX as TotalQtyConv, SIW.LTR, LINEAMOUNT, DISC_AMOUNT, SEC_DISC_AMOUNT, DISC, TAX_CODE, TAX_AMOUNT, ADDTAX_CODE, "
// + " ADDTAX_AMOUNT, AMOUNT,TD_Per,PE_Per,tdvalue FROM ACX_SALESUMMARY_PARTY_ITEM_WISE SIW"
// + " INNER JOIN AX.INVENTTABLE INVT ON SIW.PRODUCT_CODE = INVT.ITEMID "
// + " LEFT JOIN [ax].[ACXCUSTMASTER] C on SIW.Customer_Code = C.CUSTOMER_CODE"
// + " where SITEID = '" + Session["SiteCode"].ToString() + "' and INVOICE_DATE >=" +
// " '" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "' and INVOICE_DATE <='" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "' " +
// " ORDER BY INVOICE_DATE ASC , INVOICE_NO ASC ";
List <string> param = new List <string>();
List <string> paramVal = new List <string>();
param.Add("@SiteId");
paramVal.Add(Session["SiteCode"].ToString());
param.Add("@StartDate");
paramVal.Add(Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd"));
param.Add("@EndDate");
paramVal.Add(Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd"));
param.Add("@customergroupname");
paramVal.Add(CustomerGroup);
param.Add("@customername");
paramVal.Add(Customer);
param.Add("@BUCODE");
paramVal.Add(BU);
DataTable dtSetData = obj.GetData_New(FilterQuery, CommandType.StoredProcedure, param, paramVal);
//dtSetData = new DataTable();
//dtSetData = obj.GetData(FilterQuery);
string queryTotInv = " Select Count(Distinct INVOICE_NO) as InvoiceNo FROM ACX_SALESUMMARY_PARTY_ITEM_WISE " +
" where SITEID = '" + Session["SiteCode"].ToString() + "' and INVOICE_DATE >=" +
" '" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "' and INVOICE_DATE <='" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "' group by CUSTOMER_NAME";
DataTable dtTotalInvoiceNo = obj.GetData(queryTotInv);
LoadDataInReportViewer(dtSetHeader, dtSetData, dtTotalInvoiceNo);
}
catch (Exception ex)
{
LblMessage.Text = ex.Message.ToString();
ErrorSignal.FromCurrentContext().Raise(ex);
}
}