protected void ddlState_SelectedIndexChanged(object sender, EventArgs e)
{
string usertype = string.Empty;
string sqlstr = "select * from ax.ACXSITEMENU where SITE_CODE ='" + Session["SiteCode"].ToString() + "'";
object objcheckSitecode = baseObj.GetScalarValue(sqlstr);
if (objcheckSitecode != null)
{
usertype = ddlUserType.SelectedItem.Value.ToString();
DataTable dtDataByfilter = null;
string query = string.Empty;
try
{
ddlUsers.Items.Clear();
conn = new SqlConnection(baseObj.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 3600;
cmd.CommandType = CommandType.StoredProcedure;
query = "USP_FILLUSERSINDROPDOWN";
cmd.CommandText = query;
if (ddlState.SelectedItem.Text == "ALL")
{
cmd.Parameters.AddWithValue("@STATECODE", "");
}
else
{
cmd.Parameters.AddWithValue("@STATECODE", ddlState.SelectedItem.Value);
}
cmd.Parameters.AddWithValue("@USERTYPE", usertype);
dtDataByfilter = new DataTable();
dtDataByfilter.Load(cmd.ExecuteReader());
if (dtDataByfilter.Rows.Count > 0)
{
ddlUsers.DataSource = dtDataByfilter;
ddlUsers.DataTextField = "Name";
ddlUsers.DataValueField = "Code";
ddlUsers.DataBind();
}
ddlUsers.Items.Insert(0, "ALL");
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
private void ExcelDownloadGSTR2()
{
try
{
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
SqlConnection conn = new SqlConnection(obj.GetConnectionString());
SqlCommand cmd = new SqlCommand();
DataTable dtDataByfilter;
SqlDataAdapter ad;
DataSet dsDataByfilter = new DataSet();
string query = string.Empty;
conn.Open();
cmd.Connection = conn;
cmd.CommandTimeout = 3600;
cmd.CommandType = CommandType.StoredProcedure;
#region Generating Data For GSTR1_B2B
cmd.CommandText = "USP_GSTR2_B2B";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@SITEID", Session["SiteCode"].ToString());
cmd.Parameters.AddWithValue("@FROMMONTH", Convert.ToDateTime(txtFromDate.Text));
cmd.Parameters.AddWithValue("@TOMONTH", Convert.ToDateTime(txtToDate.Text));
dtDataByfilter = new DataTable("B2B");
ad = new SqlDataAdapter(cmd);
ad.Fill(dtDataByfilter);
dsDataByfilter.Tables.Add(dtDataByfilter);
#endregion
#region Generating Data For GSTR1_CDNR
cmd.CommandText = "USP_GSTR2_CDNR";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@SITEID", Session["SiteCode"].ToString());
cmd.Parameters.AddWithValue("@FROMMONTH", Convert.ToDateTime(txtFromDate.Text));
cmd.Parameters.AddWithValue("@TOMONTH", Convert.ToDateTime(txtToDate.Text));
dtDataByfilter = new DataTable("CDNR");
ad = new SqlDataAdapter(cmd);
ad.Fill(dtDataByfilter);
dsDataByfilter.Tables.Add(dtDataByfilter);
#endregion
#region Generating Data For GSTR1_B2B
cmd.CommandText = "USP_GSTR2_HSN";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@SITEID", Session["SiteCode"].ToString());
cmd.Parameters.AddWithValue("@FROMMONTH", Convert.ToDateTime(txtFromDate.Text));
cmd.Parameters.AddWithValue("@TOMONTH", Convert.ToDateTime(txtToDate.Text));
dtDataByfilter = new DataTable("hsnsum");
ad = new SqlDataAdapter(cmd);
ad.Fill(dtDataByfilter);
dsDataByfilter.Tables.Add(dtDataByfilter);
#endregion
ExcelCreationGSTR2(dsDataByfilter);
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
}
}
private void SaveData()
{
string query = string.Empty;
try
{
if (Session["ServiceLineItem"] != null)
{
DataTable dt = new DataTable();
DataSet lds = new DataSet();
dt = (DataTable)Session["ServiceLineItem"];
dt.TableName = "ServiceLineItems";
lds.Tables.Add(dt);
string ls_xml = lds.GetXml();
conn = new SqlConnection(baseObj.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
transaction = conn.BeginTransaction();
cmd.Connection = conn;
cmd.CommandTimeout = 3600;
cmd.Transaction = transaction;
cmd.CommandType = CommandType.StoredProcedure;
query = "USP_INS_SERVICEBILL";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
cmd.Parameters.AddWithValue("@SITEID", Session["SiteCode"].ToString());
cmd.Parameters.AddWithValue("@COMPANYCODE", ddlCompany.SelectedItem.Value.ToString());
if (drpCustomer.Visible == false)
{
cmd.Parameters.AddWithValue("@CUSTOMERCODE", "");
}
else
{
cmd.Parameters.AddWithValue("@CUSTOMERCODE", ddlCustomers.SelectedItem.Value.ToString());
}
cmd.Parameters.AddWithValue("@REFRENCEDOCNO", "");
cmd.Parameters.AddWithValue("@REFRENCEDATE", Convert.ToDateTime(txtMonth.Text.ToString()));
cmd.Parameters.AddWithValue("@REMARK", "");
cmd.Parameters.AddWithValue("@USERCODE", Session["USERID"].ToString());
cmd.Parameters.AddWithValue("@XmlData", ls_xml);
SqlParameter DocumentNo = new SqlParameter();
DocumentNo.ParameterName = "@DOCUMENTNO";
DocumentNo.SqlDbType = SqlDbType.NVarChar;
DocumentNo.Size = 20;
DocumentNo.Direction = ParameterDirection.Output;
cmd.Parameters.Add(DocumentNo);
cmd.ExecuteNonQuery();
transaction.Commit();
InvoiceNo = cmd.Parameters["@DOCUMENTNO"].Value.ToString();
ResetData();
}
else
{
lblMessage.Visible = true;
lblMessage.Text = "► Cannot insert data because session in Empty";
}
}
catch (Exception ex)
{
transaction.Rollback();
lblMessage.Visible = true;
lblMessage.Text = "► " + ex.Message.ToString();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
if (conn != null)
{
if (conn.State.ToString() == "Open")
{
conn.Close();
}
}
}
}
private void ShowReportByFilter()
{
CreamBell_DMS_WebApps.App_Code.Global objGlobal = new CreamBell_DMS_WebApps.App_Code.Global();
SqlConnection conn = null;
SqlCommand cmd = null;
DataTable dtDataByfilter = null;
string query = string.Empty;
try
{
conn = new SqlConnection(objGlobal.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 3600;
cmd.CommandType = CommandType.StoredProcedure;
query = "[dbo].[ACX_DESTRUCTIONREPORT]";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@FROMDATE", Convert.ToDateTime(txtFromDate.Text));
cmd.Parameters.AddWithValue("@TODATE", Convert.ToDateTime(txtToDate.Text));
DataTable dtState = fillStateCode();
if (dtState.Rows.Count > 0)
{
cmd.Parameters.AddWithValue("@STATECODE", dtState.Rows[0]["Code"]);
}
DataTable dtSite = fillSiteCode();
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
cmd.Parameters.AddWithValue("@SITEID", ucRoleFilters.GetCommaSepartedSiteId());
}
else
{
if (dtState.Rows.Count > 0)
{
cmd.Parameters.AddWithValue("@SITEID", dtSite.Rows[0]["Code"]);
}
}
//TypeReport
string ReportType = "";
string TypeName = "";
foreach (ListItem item in chkSelectionNew.Items)
{
if (item.Selected)
{
if (ReportType == "")
{
ReportType += "" + item.Value.ToString() + "";
TypeName += "" + item.Text.ToString() + "";
}
else
{
ReportType += "," + item.Value.ToString() + "";
TypeName += "," + item.Text.ToString() + "";
}
}
}
//if (chkAllUnit.Checked == true)
//{
// TypeName = "All";
//}
if (ReportType.Length > 0)
{
cmd.Parameters.AddWithValue("@ReportType", ReportType);
}
else
{
TypeName = "All";
cmd.Parameters.AddWithValue("@ReportType", "");
}
if (DDLBusinessUnitNew.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@BUCODE", DDLBusinessUnitNew.SelectedItem.Value.ToString());
}
else
{
cmd.Parameters.AddWithValue("@BUCODE", "");
}
dtDataByfilter = new DataTable();
dtDataByfilter.Load(cmd.ExecuteReader());
if (dtDataByfilter.Rows.Count > 0)
{
DataTable DataSetParameter = new DataTable();
DataSetParameter.Columns.Add("FROMDATE");
DataSetParameter.Columns.Add("TODATE");
DataSetParameter.Columns.Add("STATECODE");
DataSetParameter.Columns.Add("SITEID");
DataSetParameter.Columns.Add("TypeName");
DataSetParameter.Rows.Add();
DataSetParameter.Rows[0]["FROMDATE"] = txtFromDate.Text;
DataSetParameter.Rows[0]["TODATE"] = txtToDate.Text;
DataSetParameter.Rows[0]["STATECODE"] = dtState.Rows[0]["Name"];
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
DataSetParameter.Rows[0]["SITEID"] = ucRoleFilters.GetCommaSepartedSiteId();
}
else
{
DataSetParameter.Rows[0]["SITEID"] = dtSite.Rows[0]["Name"];
}
DataSetParameter.Rows[0]["TypeName"] = TypeName;
ReportViewer1.LocalReport.ReportPath = Server.MapPath("Reports\\DistructionReport.rdl");
ReportParameter FromDate = new ReportParameter();
FromDate.Name = "FromDate";
FromDate.Values.Add(txtFromDate.Text);
ReportParameter ToDate = new ReportParameter();
ToDate.Name = "ToDate";
ToDate.Values.Add(txtToDate.Text);
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportViewer1.AsyncRendering = true;
ReportDataSource RDS1 = new ReportDataSource("DataSet1", dtDataByfilter);
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(RDS1);
ReportDataSource RDS2 = new ReportDataSource("DataSet2", DataSetParameter);
ReportViewer1.LocalReport.DataSources.Add(RDS2);
this.ReportViewer1.LocalReport.Refresh();
ReportViewer1.Visible = true;
LblMessage.Text = String.Empty;
}
else
{
LblMessage.Text = "No Records Exists !!";
ReportViewer1.Visible = false;
}
}
catch (Exception ex)
{
this.LblMessage.Visible = true;
this.LblMessage.Text = "► " + ex.Message.ToString();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
if (conn != null)
{
if (conn.State.ToString() == "Open")
{
conn.Close();
}
}
}
}
private void ShowData_ForExcel()
{
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
string FilterQuery = string.Empty;
SqlConnection conn = null;
SqlCommand cmd = null;
string query = string.Empty;
try
{
conn = new SqlConnection(obj.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
query = "ax.ACX_SCHEMECLAIMREPORT";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(txtFromDate.Text));
cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text));
if (ddlState.SelectedItem.Text != "All...")
{
cmd.Parameters.AddWithValue("@STATECODE", ddlState.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@STATECODE", "");
}
// site
if (ddlSiteId.SelectedItem.Text != "All...")
{
cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@SiteId", "");
}
// CustGroup
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", "");
}
// Scheme Code
string SchemeList = "";
foreach (ListItem item in chkScheme.Items)
{
if (item.Selected)
{
if (SchemeList == "")
{
SchemeList += "" + item.Value.ToString() + "";
}
else
{
SchemeList += "," + item.Value.ToString() + "";
}
}
}
if (SchemeList.Length > 0)
{
cmd.Parameters.AddWithValue("@Scheme", SchemeList);
}
else
{
cmd.Parameters.AddWithValue("@Scheme", "");
}
string buunites = "";
foreach (ListItem items in ChkBunt.Items)
{
if (items.Selected)
{
if (buunites == "")
{
buunites += "" + items.Value.ToString() + "";
}
else
{
buunites += "," + items.Value.ToString() + "";
}
}
}
if (buunites.Length > 0)
{
cmd.Parameters.AddWithValue("@BUCODE", buunites);
}
else
{
cmd.Parameters.AddWithValue("@BUCODE", "");
}
DataTable dt = new DataTable();
dt = new DataTable();
dt.Load(cmd.ExecuteReader());
decimal BASICRATE = dt.AsEnumerable().Sum(row => row.Field <decimal>("BASICRATE"));
decimal INVOICEVALUE = dt.AsEnumerable().Sum(row => row.Field <decimal>("INVOICE VALUE"));
//decimal EXPENSEVALUE = dt.AsEnumerable().Sum(row => row.Field<decimal>("EXPENSE VALUE"));
decimal BASICVALUE = dt.AsEnumerable().Sum(row => row.Field <decimal>("BASIC VALUE"));
decimal LTR = dt.AsEnumerable().Sum(row => row.Field <decimal>("LTR"));
decimal TOTALQTY = dt.AsEnumerable().Sum(row => row.Field <decimal>("TOTAL QTY"));
decimal SCHVALUE = dt.AsEnumerable().Sum(row => row.Field <decimal>("ADD SCH VALUE"));
//////--------------------------------------
DataGrid dg = new DataGrid();
dg.DataSource = dt;
dg.DataBind();
string sFileName = "SchemeReport" + " - " + System.DateTime.Now.Date + ".xls";
sFileName = sFileName.Replace("/", "");
// SEND OUTPUT TO THE CLIENT MACHINE USING "RESPONSE OBJECT".
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=" + sFileName);
Response.ContentType = "application/vnd.ms-excel";
EnableViewState = false;
System.IO.StringWriter objSW = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter objHTW = new System.Web.UI.HtmlTextWriter(objSW);
dg.HeaderStyle.Font.Bold = true; // SET EXCEL HEADERS AS BOLD.
dg.RenderControl(objHTW);
string name = "Scheme Report";
string DistributoName = ddlSiteId.SelectedItem.Value + " - " + ddlSiteId.SelectedItem.Text;
Response.Write("<table><tr><td colspan='3' style='width:100px;align-items:center; font:18px;'> <b> " + name + "</b> </td></tr> <tr><td colspan='3'> <b> Distributor Name : " + DistributoName + " </td></tr><tr><td><b>From Date: " + txtFromDate.Text.Replace(",", "") + "</b></td><td></td> <td><b>To Date: " + txtToDate.Text.Replace(",", "") + "</b></td></tr></table>");
// STYLE THE SHEET AND WRITE DATA TO IT.
Response.Write("<style> TABLE { border:dotted 1px #999; } " +
"TD { border:dotted 1px #D5D5D5; text-align:center } </style>");
Response.Write(objSW.ToString());
// ADD A ROW AT THE END OF THE SHEET SHOWING A RUNNING TOTAL OF PRICE.
//Response.Write("<table><tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td><b>Total:</b></td><td><b>" + BASICRATE + "</b></td><td><b>" + INVOICEVALUE + " </b></td><td ><b>" + TOTALQTY + "</b></td><td ><b>" + LTR + "</b></td><td ><b>" + BASICVALUE + "</b></td><td ><b>" + SCHVALUE + "</b></td><td ><b>" + EXPENSEVALUE + "</b></td></tr></table>");
Response.Write("<table><tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td><b>Total:</b></td><td><b>" + BASICRATE + "</b></td><td><b>" + INVOICEVALUE + " </b></td><td ><b>" + TOTALQTY + "</b></td><td ><b>" + LTR + "</b></td><td ><b>" + BASICVALUE + "</b></td><td ><b>" + SCHVALUE + "</b></td></tr></table>");
Response.End();
dg = null;
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
}
}
private void ShowReportByFilter()
{
CreamBell_DMS_WebApps.App_Code.Global objGlobal = new CreamBell_DMS_WebApps.App_Code.Global();
SqlConnection conn = null;
SqlCommand cmd = null;
DataTable dtDataByfilter = null;
string query = string.Empty;
try
{
conn = new SqlConnection(objGlobal.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
query = "ax.ACX_SCHEMECLAIMREPORT";
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));
cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text));
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
cmd.Parameters.AddWithValue("@STATECODE", ucRoleFilters.GetCommaSepartedStateId());
cmd.Parameters.AddWithValue("@SiteId", ucRoleFilters.GetCommaSepartedSiteId());
}
else
{
string State = string.Empty;
if (ddlState.SelectedItem.Text == "All...")
{
cmd.Parameters.AddWithValue("@STATECODE", "");
}
else
{
cmd.Parameters.AddWithValue("@STATECODE", ddlState.SelectedItem.Value);
}
if (ddlSiteId.SelectedIndex != -1)
{
if (ddlSiteId.SelectedItem.Text == "All...")
{
cmd.Parameters.AddWithValue("@SiteId", "");
}
else
{
cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value);
}
}
}
// CustGroup
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", "");
}
// Scheme Code
string SchemeList = "";
foreach (ListItem item in chkScheme.Items)
{
if (item.Selected)
{
if (SchemeList == "")
{
SchemeList += "" + item.Value.ToString() + "";
}
else
{
SchemeList += "," + item.Value.ToString() + "";
}
}
}
if (SchemeList.Length > 0)
{
cmd.Parameters.AddWithValue("@Scheme", SchemeList);
}
else
{
cmd.Parameters.AddWithValue("@Scheme", "");
}
string buunit = "";
foreach (ListItem items in ChkBunt.Items)
{
if (items.Selected)
{
if (buunit == "")
{
buunit += "" + items.Value.ToString() + "";
}
else
{
buunit += "," + items.Value.ToString() + "";
}
}
}
if (buunit.Length > 0)
{
cmd.Parameters.AddWithValue("@BUCODE", buunit);
}
else
{
cmd.Parameters.AddWithValue("@BUCODE", "");
}
dtDataByfilter = new DataTable();
dtDataByfilter.Load(cmd.ExecuteReader());
LoadDataInReportViewer(dtDataByfilter);
}
catch (Exception ex)
{
this.LblMessage.Visible = true;
this.LblMessage.Text = "► " + ex.Message.ToString();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
if (conn != null)
{
if (conn.State.ToString() == "Open")
{
conn.Close();
}
}
}
}
protected void BindGridview()
{
try
{
//string condition = string.Empty;
//if (drpPlant.SelectedItem.Value != "--All--")
//{
// condition = " Where A.[ACXPLANTCODE]='" + drpPlant.SelectedItem.Value + "'";
//}
//else
//{
//}
//query = "Select ACXPLANTCODE,ACXPLANTCODE+'-'+ACXPLANTNAME as PlantName from (" +
// " Select CASE WHEN LEN(A.[ACXPLANTCODE])>0 THEN A.[ACXPLANTCODE] ELSE A.[ACXPLANTNAME] END [ACXPLANTCODE], " +
// " CASE WHEN LEN(A.[ACXPLANTCODE])>0 THEN A.[ACXPLANTNAME] " +
// " ELSE (SELECT TOP 1 NAME FROM AX.INVENTSITE WHERE SITEID=A.ACXPLANTNAME) END [ACXPLANTNAME] " +
// " From ax.inventsite A Where A.SITEID='" + Session["SITECODE"].ToString() + "' " +
// " Union " +
// " Select SITEID,NAME " +
// " From AX.INVENTSITE A " +
// " Where A.SITEID IN (SELECT DISTINCT OTHER_SITE FROM AX.ACX_SDLinking WHERE SubDistributor='" + Session["SITECODE"].ToString() + "' AND BLOCKED=0) ) A " + condition;
string query = string.Empty;
conn = new SqlConnection(obj.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 3600;
cmd.CommandType = CommandType.StoredProcedure;
if (drpPlant.SelectedItem.Text != "--All--")
{
query = "acx_getpendingpaymententry " + "'" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "'," + "'" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "'," + "'" + drpPlant.SelectedItem.Value + "','" + Session["SITECODE"] + "'";
//cmd.Parameters.AddWithValue("@SupplierCode", drpPlant.SelectedItem.Value);
}
else
{
query = "acx_getpendingpaymententry " + "'" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "'," + "'" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "'," + "'','" + Session["SITECODE"] + "'";
//cmd.Parameters.AddWithValue("@SupplierCode", "");
}
cmd.CommandText = query;
dt = new DataTable();
dt = baseObj.GetData(query);
if (dt.Rows.Count > 0)
{
gvDetails.DataSource = dt;
gvDetails.DataBind();
}
else
{
gvDetails.DataSource = dt;
gvDetails.DataBind();
}
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
Console.Write(ex.Message);
}
}
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 dtTotalInvoiceNo = null;
try
{
DateTime Todate = Convert.ToDateTime(txtFromDate.Text);
// DateTime FromDate = DateTime.Today.AddDays(1 - Todate.Day);
// DateTime date = DateTime.Now;
var firstDayOfMonth = new DateTime(Todate.Year, Todate.Month, 1);
DateTime FromDate = firstDayOfMonth;
string query1 = "Select NAME from ax.inventsite where SITEID='" + Session["SiteCode"].ToString() + "'";
dtSetHeader = new DataTable();
dtSetHeader = obj.GetData(query1);
SqlConnection conn = null;
SqlCommand cmd = null;
DataTable dtDataByfilter = null;
string query = string.Empty;
conn = new SqlConnection(obj.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
query = "[ax].[ACX_DailySaleTrackingReport]";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(FromDate));
cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(Todate));
cmd.Parameters.AddWithValue("@UserType", Convert.ToString(Session["LOGINTYPE"]));
cmd.Parameters.AddWithValue("@UserCode", Convert.ToString(Session["USERID"]));
string SiteList = "";
foreach (ListItem item in lstDIS.Items)
{
if (item.Selected)
{
if (SiteList == "")
{
SiteList += "" + item.Value.ToString() + "";
}
else
{
SiteList += "," + item.Value.ToString() + "";
}
}
}
if (SiteList.Length > 0)
{
cmd.Parameters.AddWithValue("@SiteId", SiteList);
}
else
{
cmd.Parameters.AddWithValue("@SiteId", "");
}
string CustGroupList = "";
foreach (ListItem item in lstPSRNAME.Items)
{
if (item.Selected)
{
if (CustGroupList == "")
{
CustGroupList += "" + item.Text.ToString() + "";
}
else
{
CustGroupList += "," + item.Text.ToString() + "";
}
}
}
if (CustGroupList.Length > 0)
{
cmd.Parameters.AddWithValue("@PSR_Code", CustGroupList);
}
else
{
cmd.Parameters.AddWithValue("@PSR_Code", "");
}
string ListState = "";
foreach (ListItem item in lstSTATE.Items)
{
if (item.Selected)
{
if (ListState == "")
{
ListState += "" + item.Value.ToString() + "";
}
else
{
ListState += "," + item.Value.ToString() + "";
}
}
}
if (ListState.Length > 0)
{
cmd.Parameters.AddWithValue("@STATECODE", ListState);
}
else
{
cmd.Parameters.AddWithValue("@STATECODE", "");
}
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 = '" + Session["SiteCode"].ToString() + "' and INVOICE_DATE >=" +
// " '" + Convert.ToDateTime(txtFromDate.Text) + "' and INVOICE_DATE <='" + Convert.ToDateTime(txtToDate.Text) + "' group by SP.CUSTOMER_NAME";
//dtSetData = new DataTable();
//dtSetData = dtDataByfilter;
dtTotalInvoiceNo = null;
LoadDataInReportViewer(dtSetHeader, dtDataByfilter, dtTotalInvoiceNo);
}
catch (Exception ex)
{
LblMessage.Text = ex.Message.ToString();
ErrorSignal.FromCurrentContext().Raise(ex);
}
}
private void ShowReportByFilter()
{
CreamBell_DMS_WebApps.App_Code.Global objGlobal = new CreamBell_DMS_WebApps.App_Code.Global();
SqlConnection conn = null;
SqlCommand cmd = null;
DataTable dtDataByfilter = null;
string query = string.Empty;
try
{
conn = new SqlConnection(objGlobal.GetConnectionString());
//conn.Open();
cmd = new SqlCommand();
//cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
query = "[ax].[ACX_MRPDiscountClaimReport]";
cmd.CommandText = query;
//DateTime lastDay = new DateTime(now.Year, now.Month, 1);
cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(txtFromDate.Text).ToString("dd-MMM-yyy"));
cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text).ToString("dd-MMM-yyy"));
string ListState = "";
string DistributorList = "";
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
ListState = ucRoleFilters.GetCommaSepartedStateId();
DistributorList = ucRoleFilters.GetCommaSepartedSiteId();
}
else
{
foreach (ListItem item in chkListState.Items)
{
if (item.Selected)
{
if (ListState == "")
{
ListState += "" + item.Value.ToString() + "";
}
else
{
ListState += "," + item.Value.ToString() + "";
}
}
}
foreach (ListItem item in chkListSite.Items)
{
if (item.Selected)
{
if (DistributorList == "")
{
DistributorList += "" + item.Value.ToString() + "";
}
else
{
DistributorList += "," + item.Value.ToString() + "";
}
}
}
}
if (ListState.Length > 0)
{
cmd.Parameters.AddWithValue("@STATECODE", ListState);
}
else
{
cmd.Parameters.AddWithValue("@STATECODE", "");
}
if (DistributorList.Length > 0)
{
cmd.Parameters.AddWithValue("@SiteId", DistributorList);
}
else
{
cmd.Parameters.AddWithValue("@SiteId", "");
}
string CustGroupList = "";
foreach (ListItem item in chkListCustomerGroup.Items)
{
if (item.Selected)
{
if (CustGroupList == "")
{
CustGroupList += "" + item.Value.ToString() + "";
}
else
{
CustGroupList += "," + item.Value.ToString() + "";
}
}
}
if (CustGroupList.Length > 0)
{
cmd.Parameters.AddWithValue("@CUSTOMER_CODE", CustGroupList);
}
else
{
cmd.Parameters.AddWithValue("@CUSTOMER_CODE", "");
}
string buunits = "";
foreach (ListItem items in Chkbu.Items)
{
if (items.Selected)
{
if (buunits == "")
{
buunits += "" + items.Value.ToString() + "";
}
else
{
buunits += "," + items.Value.ToString() + "";
}
}
}
if (buunits.Length > 0)
{
cmd.Parameters.AddWithValue("@BUCODE", buunits);
}
else
{
cmd.Parameters.AddWithValue("@BUCODE", "");
}
dtDataByfilter = new DataTable();
cmd.Connection = objGlobal.GetConnection();
dtDataByfilter.Load(cmd.ExecuteReader());
LoadDataInReportViewer(dtDataByfilter);
}
catch (Exception ex)
{
this.LblMessage.Visible = true;
this.LblMessage.Text = "► " + ex.Message.ToString();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
//if (conn != null)
//{
// if (conn.State.ToString() == "Open") { conn.Close(); }
//}
objGlobal.CloseSqlConnection();
}
}
private void ShowReportByFilter()
{
CreamBell_DMS_WebApps.App_Code.Global objGlobal = new CreamBell_DMS_WebApps.App_Code.Global();
SqlConnection conn = null;
SqlCommand cmd = null;
DataTable dtDataByfilter = null;
string query = string.Empty;
try
{
conn = new SqlConnection(objGlobal.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
query = "ACX_USP_GET_ADJUSTMENTENTRY_REPORT";
cmd.CommandText = query;
//DateTime lastDay = new DateTime(now.Year, now.Month, 1);
cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(txtFromDate.Text).ToString("dd-MMM-yyy"));
cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text).ToString("dd-MMM-yyy"));
string strStateCode = "";
string strSiteCode = "";
//if (Convert.ToString(Session["LOGINTYPE"]) == "3") {
// strSiteCode = ucRoleFilters.GetCommaSepartedSiteId();
// strStateCode = ucRoleFilters.GetCommaSepartedStateId();
//} else {
foreach (ListItem item in lstState.Items)
{
if (item.Selected)
{
if (strStateCode == "")
{
strStateCode += "" + item.Value.ToString() + "";
}
else
{
strStateCode += "," + item.Value.ToString() + "";
}
}
}
foreach (ListItem item in lstSiteId.Items)
{
if (item.Selected)
{
if (strSiteCode == "")
{
strSiteCode += "" + item.Value.ToString() + "";
}
else
{
strSiteCode += "," + item.Value.ToString() + "";
}
}
}
//}
if (strStateCode.Length > 0)
{
cmd.Parameters.AddWithValue("@STATECODE", strStateCode);
}
else
{
cmd.Parameters.AddWithValue("@STATECODE", "");
}
if (strSiteCode.Length > 0)
{
cmd.Parameters.AddWithValue("@SiteCode", strSiteCode);
}
else
{
cmd.Parameters.AddWithValue("@SiteCode", "");
}
dtDataByfilter = new DataTable();
dtDataByfilter.Load(cmd.ExecuteReader());
LoadDataInReportViewer(dtDataByfilter);
}
catch (Exception ex)
{
this.LblMessage.Visible = true;
this.LblMessage.Text = "► " + ex.Message.ToString();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
if (conn != null)
{
if (conn.State.ToString() == "Open")
{
conn.Close();
}
}
}
}
private void ShowReportByFilter()
{
CreamBell_DMS_WebApps.App_Code.Global objGlobal = new CreamBell_DMS_WebApps.App_Code.Global();
SqlConnection conn = null;
SqlCommand cmd = null;
DataTable dtDataByfilter = null;
string query = string.Empty;
try
{
conn = new SqlConnection(objGlobal.GetConnectionString());
// conn.Open();
cmd = new SqlCommand();
// cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
query = "[dbo].[ACX_USP_SALERANKINGREPORT]";
cmd.CommandText = query;
DateTime now = Convert.ToDateTime(txtToDate.Text);
now = now.AddMonths(1);
//DateTime lastDay = new DateTime(now.Year, now.Month, 1);
cmd.Parameters.AddWithValue("@STARTDATE", Convert.ToDateTime(txtFromDate.Text));
cmd.Parameters.AddWithValue("@ENDDATE", Convert.ToDateTime(txtToDate.Text));
cmd.Parameters.AddWithValue("@UserType", Convert.ToString(Session["LOGINTYPE"]));
cmd.Parameters.AddWithValue("@UserCode", Convert.ToString(Session["USERID"]));
if (drpCustGroup.SelectedItem.Text == "All...")
{
cmd.Parameters.AddWithValue("@CustGroup", "");
}
else
{
cmd.Parameters.AddWithValue("@CustGroup", drpCustGroup.SelectedItem.Value.ToString());
}
cmd.Parameters.AddWithValue("@State", ddlState.SelectedItem.Value.ToString());
//if (ddlSiteId.SelectedIndex >= 1)
//{
// cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value);
//}
//else
//{
// cmd.Parameters.AddWithValue("@SiteId", " ");
//}
if (ddlSiteId.SelectedIndex < 0)
{
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
string siteid = "";
DataTable dt = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE);
foreach (DataRow row in dt.Rows)
{
if (siteid == "")
{
siteid = Convert.ToString(row["DISTRIBUTOR"]);
}
else
{
siteid += "," + row["DISTRIBUTOR"];
}
}
}
}
else if (ddlSiteId.SelectedIndex > 0)
{
cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value);
}
else if (ddlSiteId.SelectedItem.Text != "All...")
{
cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value);
}
else
{
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
string siteid = "";
DataTable dt = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE);
foreach (DataRow row in dt.Rows)
{
if (siteid == "")
{
siteid = Convert.ToString(row["DISTRIBUTOR"]);
}
else
{
siteid += "," + row["DISTRIBUTOR"];
}
}
cmd.Parameters.AddWithValue("@SiteId", siteid);
}
else
{
cmd.Parameters.AddWithValue("@SiteId", "");
}
}
if (DDLBusinessUnit.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@BUCODE", DDLBusinessUnit.SelectedItem.Value.ToString());
}
else
{
cmd.Parameters.AddWithValue("@BUCODE", "");
}
dtDataByfilter = new DataTable();
cmd.Connection = objGlobal.GetConnection();
dtDataByfilter.Load(cmd.ExecuteReader());
LoadDataInReportViewer(dtDataByfilter);
}
catch (Exception ex)
{
this.LblMessage.Visible = true;
this.LblMessage.Text = "► " + ex.Message.ToString();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
objGlobal.CloseSqlConnection();
}
}
private void ShowReportByFilter()
{
CreamBell_DMS_WebApps.App_Code.Global objGlobal = new CreamBell_DMS_WebApps.App_Code.Global();
SqlConnection conn = null;
SqlCommand cmd = null;
DataTable dtDataByfilter = null;
string query = string.Empty;
try
{
conn = new SqlConnection(objGlobal.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
query = "ACX_USP_DEEPFREZEERSALESREPORT";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@STARTDATE", Convert.ToDateTime(txtFromDate.Text));
cmd.Parameters.AddWithValue("@ENDDATE", Convert.ToDateTime(txtToDate.Text));
cmd.Parameters.AddWithValue("@UserType", Convert.ToString(Session["LOGINTYPE"]));
cmd.Parameters.AddWithValue("@UserCode", Convert.ToString(Session["USERID"]));
if (ddlState.SelectedIndex > 0 || ddlState.SelectedItem.Text != "All...")
{
cmd.Parameters.AddWithValue("@StateCode", ddlState.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@StateCode", "");
}
// site
if (ddlSiteId.SelectedIndex < 0)
{
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
string siteid = "";
DataTable dt = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE);
foreach (DataRow row in dt.Rows)
{
if (siteid == "")
{
siteid = Convert.ToString(row["DISTRIBUTOR"]);
}
else
{
siteid += "," + row["DISTRIBUTOR"];
}
}
}
else
{
cmd.Parameters.AddWithValue("@SiteId", "");
}
}
else if (ddlSiteId.SelectedIndex > 0)
{
cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value);
}
else if (ddlSiteId.SelectedItem.Text != "All...")
{
cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value);
}
else
{
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
string siteid = "";
DataTable dt = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE);
DataView view = new DataView(dt);
if (ddlState.SelectedItem.Text != "All...")
{
view.RowFilter = "STATE='" + ddlState.SelectedItem.Value.Trim() + "'";
}
DataTable uniqueCols = view.ToTable(true, "Distributor", "DistributorName");
foreach (DataRow row in uniqueCols.Rows)
{
if (siteid == "")
{
siteid = Convert.ToString(row["DISTRIBUTOR"]);
}
else
{
siteid += "," + row["DISTRIBUTOR"];
}
}
cmd.Parameters.AddWithValue("@SiteId", siteid);
}
else
{
cmd.Parameters.AddWithValue("@SiteId", "");
}
}
if (drpCategory.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@Category", drpCategory.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@Category", "");
}
if (drpSubCategory.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@SubCategory", drpSubCategory.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@SubCategory", "");
}
if (drpProduct.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@Product", drpProduct.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@Product", "");
}
if (drpCustGroup.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@CustGroup", drpCustGroup.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@CustGroup", "");
}
dtDataByfilter = new DataTable();
dtDataByfilter.Load(cmd.ExecuteReader());
if (dtDataByfilter.Rows.Count > 0)
{
//=================Create Excel==========
string attachment = "attachment; filename=DEEPFREZEERREPORT.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
string tab = "";
foreach (DataColumn dc in dtDataByfilter.Columns)
{
Response.Write(tab + dc.ColumnName);
tab = "\t";
}
Response.Write("\n");
int i;
foreach (DataRow dr in dtDataByfilter.Rows)
{
tab = "";
for (i = 0; i < dtDataByfilter.Columns.Count; i++)
{
Response.Write(tab + dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.Flush();
Response.SuppressContent = true;
}
}
catch (Exception ex)
{
this.LblMessage.Visible = true;
this.LblMessage.Text = "► " + ex.Message.ToString();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
if (conn != null)
{
if (conn.State.ToString() == "Open")
{
conn.Close();
}
}
}
}
protected 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='" + Session["SiteCode"].ToString() + "'";
dtSetHeader = new DataTable();
dtSetHeader = obj.GetData(query1);
SqlConnection conn = null;
SqlCommand cmd = 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 = "[dbo].[ACX_STOCKLEDGER]";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@FromDate", txtFromDate.Text);
cmd.Parameters.AddWithValue("@ToDate", txtToDate.Text);
//cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value);
//cmd.Parameters.AddWithValue("@ItemId", drpProduct.SelectedItem.Value);
//cmd.Parameters.AddWithValue("@TransLocation", ddlWarehouse.SelectedItem.Value);
//ItemId
if (drpProduct.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@ItemId", drpProduct.SelectedItem.Value);
}
if (drpProduct.SelectedIndex == 0 || drpProduct.Text == "All...")
{
cmd.Parameters.AddWithValue("@ItemId", "");
}
//bucode
if (ddlBusinessUnit.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@BUCODE", ddlBusinessUnit.SelectedItem.Value);
}
if (ddlBusinessUnit.SelectedIndex == 0 || drpProduct.Text == "All...")
{
cmd.Parameters.AddWithValue("@BUCODE", "");
}
// site
if (ddlSiteId.SelectedIndex > 0)
{
cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value);
}
else if (ddlSiteId.SelectedItem.Text != "All...")
{
cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value);
}
//WareLocation
if (ddlWarehouse.SelectedIndex >= 0)
{
cmd.Parameters.AddWithValue("@TransLocation", ddlWarehouse.SelectedItem.Value);
}
if (rdoDetail.Checked == true)
{
cmd.Parameters.AddWithValue("@Type", 0);
}
else
{
cmd.Parameters.AddWithValue("@Type", 1);
}
//dtDataByfilter1 = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
ad.Fill(dt);
//dtDataByfilter = new DataTable();
//dtDataByfilter.Load(cmd.ExecuteReader());
//DataTable dt = new DataTable();
//dt = dtDataByfilter;
//==================New Code=============
DataGrid dg = new DataGrid();
dg.DataSource = dt;
dg.DataBind();
// THE EXCEL FILE
string sFileName = "StockLedgerReport" + " - " + System.DateTime.Now.Date + ".xls";
sFileName = sFileName.Replace("/", "");
// SEND OUTPUT TO THE CLIENT MACHINE USING "RESPONSE OBJECT".
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=" + sFileName);
Response.ContentType = "application/vnd.ms-excel";
EnableViewState = false;
System.IO.StringWriter objSW = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter objHTW = new System.Web.UI.HtmlTextWriter(objSW);
dg.HeaderStyle.Font.Bold = true; // SET EXCEL HEADERS AS BOLD.
dg.RenderControl(objHTW);
string name = "Stock Ledger Report";
if (rdoDetail.Checked == true)
{
name = name + " - Details";
}
else
{
name = name + " - Summary";
}
string DistributoName = ddlSiteId.SelectedItem.Value + " - " + ddlSiteId.SelectedItem.Text;
Response.Write("<table><tr><td colspan='5' style='width:100px;align-items:center; font:18px;'> <b> " + name + "</b> </td></tr> <tr><td colspan='3'> <b> Distributor Name : " + DistributoName + " </td></tr><tr><td><b>From Date: " + txtFromDate.Text + "</b></td><td></td> <td><b>To Date: " + txtToDate.Text + "</b></td></tr></table>");
// STYLE THE SHEET AND WRITE DATA TO IT.
Response.Write("<style> TABLE { border:dotted 1px #999; } " +
"TD { border:dotted 1px #D5D5D5; text-align:center } </style>");
Response.Write(objSW.ToString());
// ADD A ROW AT THE END OF THE SHEET SHOWING A RUNNING TOTAL OF PRICE.
// Response.Write("<table><tr><td><b>Total: </b></td><td></td><td><b>" +"N2" + "</b></td></tr></table>");
Response.End();
dg = null;
//=================Create Excel==========
//string attachment = "attachment; filename=StockLedgerReport.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)
{
ErrorSignal.FromCurrentContext().Raise(ex);
}
}
private void ShowReportByFilter()
{
CreamBell_DMS_WebApps.App_Code.Global objGlobal = new CreamBell_DMS_WebApps.App_Code.Global();
SqlConnection conn = null;
SqlCommand cmd = null;
DataTable dtDataByfilter1 = null;
string query = string.Empty;
try
{
conn = new SqlConnection(objGlobal.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
query = "ACX_STOCKLEDGER";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@FromDate", txtFromDate.Text);
cmd.Parameters.AddWithValue("@ToDate", txtToDate.Text);
// site
if (ddlSiteId.SelectedIndex > 0)
{
cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value);
}
else if (ddlSiteId.SelectedItem.Text != "All...")
{
cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value);
}
//bucode
if (ddlBusinessUnit.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@BUCODE", ddlBusinessUnit.SelectedItem.Value);
}
if (ddlBusinessUnit.SelectedIndex == 0 || ddlBusinessUnit.SelectedItem.Text == "All...")
{
cmd.Parameters.AddWithValue("@BUCODE", "");
}
//ItemId
if (drpProduct.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@ItemId", drpProduct.SelectedItem.Value);
}
if (drpProduct.SelectedIndex == 0 || drpProduct.Text == "All...")
{
cmd.Parameters.AddWithValue("@ItemId", "");
}
//WareLocation
if (ddlWarehouse.SelectedIndex >= 0)
{
cmd.Parameters.AddWithValue("@TransLocation", ddlWarehouse.SelectedItem.Value);
}
if (rdoDetail.Checked == true)
{
cmd.Parameters.AddWithValue("@Type", 0);
}
else
{
cmd.Parameters.AddWithValue("@Type", 1);
}
dtDataByfilter1 = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
ad.Fill(dt);
//dtDataByfilter1.Load(cmd.ExecuteReader());
LoadDataInReportViewer(dt);
}
catch (Exception ex)
{
this.LblMessage.Visible = true;
this.LblMessage.Text = "► " + ex.Message.ToString();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
if (conn != null)
{
if (conn.State.ToString() == "Open")
{
conn.Close();
}
}
}
}
protected void BtnShowReport0_Click(object sender, EventArgs e)
{
CreamBell_DMS_WebApps.App_Code.Global objGlobal = new CreamBell_DMS_WebApps.App_Code.Global();
SqlConnection conn = null;
SqlCommand cmd = null;
DataTable dtDataByfilter = null;
string query = string.Empty;
try
{
conn = new SqlConnection(objGlobal.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
query = "usp_GetCustomerOutstandingForWirteoff";
cmd.CommandText = query;
string strStateCode = "";
foreach (ListItem item in chkListState.Items)
{
if (item.Selected)
{
if (strStateCode == "")
{
strStateCode += "" + item.Value.ToString() + "";
}
else
{
strStateCode += "," + item.Value.ToString() + "";
}
}
}
if (strStateCode.Length > 0)
{
cmd.Parameters.AddWithValue("@STATECODE", strStateCode);
}
else
{
cmd.Parameters.AddWithValue("@STATECODE", "");
}
string strSiteCode = "";
foreach (ListItem item in chkListSite.Items)
{
if (item.Selected)
{
if (strSiteCode == "")
{
strSiteCode += "" + item.Value.ToString() + "";
}
else
{
strSiteCode += "," + item.Value.ToString() + "";
}
}
}
if (strSiteCode.Length > 0)
{
cmd.Parameters.AddWithValue("@SiteCode", strSiteCode);
}
else
{
cmd.Parameters.AddWithValue("@SiteCode", "");
}
string strCustomerGroup = "";
foreach (ListItem item in chkListCustomerGroup.Items)
{
if (item.Selected)
{
if (strCustomerGroup == "")
{
strCustomerGroup += "" + item.Value.ToString() + "";
}
else
{
strCustomerGroup += "," + item.Value.ToString() + "";
}
}
}
if (strCustomerGroup.Length > 0)
{
cmd.Parameters.AddWithValue("@Cust_Group", strCustomerGroup);
}
else
{
cmd.Parameters.AddWithValue("@Cust_Group", "");
}
string strCustomerName = "";
foreach (ListItem item in chkCustomerName.Items)
{
if (item.Selected)
{
if (strCustomerName == "")
{
strCustomerName = item.Value.ToString();
}
else
{
strCustomerName += "," + item.Value.ToString() + "";
}
}
}
if (strCustomerName.Length > 0)
{
cmd.Parameters.AddWithValue("@Customer_Code", strCustomerName);
}
else
{
cmd.Parameters.AddWithValue("@Customer_Code", "");
}
dtDataByfilter = new DataTable();
dtDataByfilter.Load(cmd.ExecuteReader());
lblShowTotalOutStaning.Text = string.Empty;
if (dtDataByfilter != null && dtDataByfilter.Rows.Count > 0)
{
object sumObject;
sumObject = dtDataByfilter.Compute("Sum(RemainingAmount)", "");
lblShowTotalOutStaning.Text = Convert.ToString(sumObject);
gvDetails.DataSource = dtDataByfilter;
gvDetails.DataBind();
uppanel.Update();
}
else
{
gvDetails.DataSource = dtDataByfilter;
gvDetails.DataBind();
}
}
catch (Exception ex)
{
this.LblMessage.Visible = true;
this.LblMessage.Text = "► " + ex.Message.ToString();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
if (conn != null)
{
if (conn.State.ToString() == "Open")
{
conn.Close();
}
}
}
}
private void ShowReportByFilter()
{
CreamBell_DMS_WebApps.App_Code.Global objGlobal = new CreamBell_DMS_WebApps.App_Code.Global();
SqlConnection conn = null;
SqlCommand cmd = null;
SqlCommand cmd1 = null;
DataTable dtDataByfilter = null;
string query = string.Empty;
try
{
conn = new SqlConnection(objGlobal.GetConnectionString());
// conn.Open();
cmd = new SqlCommand();
// cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
query = "[ax].[ACX_MonthlySummerySheet]";
cmd.CommandText = query;
DateTime stDate = Convert.ToDateTime(txtFromDate.Text);
DateTime firstDayOfMonth = new DateTime(stDate.Year, stDate.Month, 1);
DateTime lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
cmd.Parameters.AddWithValue("@StartDate", firstDayOfMonth);
cmd.Parameters.AddWithValue("@EndDate", lastDayOfMonth);
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
cmd.Parameters.AddWithValue("@SiteId", ucRoleFilters.GetCommaSepartedSiteId());
}
else
{
if (ddlSiteId.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@SiteId", "0");
}
}
if (ddlBusinessUnit.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@BUCODE", ddlBusinessUnit.SelectedItem.Value.ToString());
}
else
{
cmd.Parameters.AddWithValue("@BUCODE", "");
}
//if (ddlState.SelectedIndex >= 1)
//{
// cmd.Parameters.AddWithValue("@SITESTATE", ddlState.SelectedItem.Value);
//}
//else
//{
// cmd.Parameters.AddWithValue("@SITESTATE", "0");
//}
dtDataByfilter = new DataTable();
cmd.Connection = objGlobal.GetConnection();
dtDataByfilter.Load(cmd.ExecuteReader());
//==============Claim Data===================
DataTable dtClaim = new DataTable();
cmd1 = new SqlCommand();
cmd1.Connection = conn;
cmd1.CommandTimeout = 0;
cmd1.CommandType = CommandType.StoredProcedure;
query = "[ax].[ACX_MonthlySummerySheet_Claim]";
cmd1.CommandText = query;
cmd1.Parameters.AddWithValue("@StartDate", firstDayOfMonth);
cmd1.Parameters.AddWithValue("@EndDate", lastDayOfMonth);
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
cmd1.Parameters.AddWithValue("@SiteId", ucRoleFilters.GetCommaSepartedSiteId());
}
else
{
if (ddlSiteId.SelectedIndex >= 1)
{
cmd1.Parameters.AddWithValue("@SiteId", ddlSiteId.SelectedItem.Value);
}
else
{
cmd1.Parameters.AddWithValue("@SiteId", "0");
}
}
if (ddlBusinessUnit.SelectedIndex >= 1)
{
cmd1.Parameters.AddWithValue("@BUCODE", ddlBusinessUnit.SelectedItem.Value.ToString());
}
else
{
cmd1.Parameters.AddWithValue("@BUCODE", "");
}
dtClaim = new DataTable();
cmd1.Connection = objGlobal.GetConnection();
dtClaim.Load(cmd1.ExecuteReader());
LoadDataInReportViewer(dtDataByfilter, dtClaim);
}
catch (Exception ex)
{
this.LblMessage.Visible = true;
this.LblMessage.Text = "► " + ex.Message.ToString();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
objGlobal.CloseSqlConnection();
}
}