protected void DDLCustGroup_SelectedIndexChanged(object sender, EventArgs e)
{
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
if (DDLCustGroup.Text == "ALL")
{
string queryALLCustomer = " Select CUSTOMER_CODE, CUSTOMER_NAME from ax.acxcustmaster where SITE_CODE='" + Session["SiteCode"].ToString() +
"' and DATAAREAID='" + Session["DATAAREAID"].ToString() + "'" +
" UNION " +
"SELECT SUBDISTRIBUTOR AS Customer_Code, SUBDISTRIBUTOR + '-' + NAME AS CUSTOMER_NAME from ax.ACX_SDLINKING where OTHER_SITE='" + Session["SiteCode"].ToString() +
"' and DATAAREAID='" + Session["DATAAREAID"].ToString() + "'";
DDLCustomers.Items.Clear();
//DDLCustomers.Items.Add("-Select-");
//obj.BindToDropDown(DDLCustomers, queryALLCustomer, "CUSTOMER_NAME", "CUSTOMER_CODE");
}
else
{
string queryCustomer = " Select CUSTOMER_CODE, CUSTOMER_NAME from ax.acxcustmaster where CUST_GROUP='" + DDLCustGroup.SelectedValue.ToString() + "' " +
" and SITE_CODE='" + Session["SiteCode"].ToString() + "' and DATAAREAID='" + Session["DATAAREAID"].ToString() + "'" +
" UNION " +
"SELECT SUBDISTRIBUTOR AS Customer_Code, SUBDISTRIBUTOR + '-' + NAME AS CUSTOMER_NAME from ax.ACX_SDLINKING where CUSTGROUP='" + DDLCustGroup.SelectedValue.ToString() + "' " +
" and OTHER_SITE='" + Session["SiteCode"].ToString() + "' and DATAAREAID='" + Session["DATAAREAID"].ToString() + "'";
//DDLCustomers.Items.Clear();
//DDLCustomers.Items.Add("-Select-");
//obj.BindToDropDown(DDLCustomers, queryCustomer, "CUSTOMER_NAME", "CUSTOMER_CODE");
CreamBell_DMS_WebApps.App_Code.CreamBellFramework.BindToDropDown(DDLCustomers, queryCustomer, "CUSTOMER_NAME", "CUSTOMER_CODE", "-Select-");
}
}
private void BindFilters()
{
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
string queryCustomerGroup = " Select CUSTGROUP_CODE, CUSTGROUP_NAME, CUSTGROUP_CODE+'-'+ CUSTGROUP_NAME as CUSTGROUP from [ax].[ACXCUSTGROUPMASTER] where DATAAREAID='" + Session["DATAAREAID"].ToString() + "' and BLOCKED<>1 ";
//DDLCustGroup.Items.Clear();
//DDLCustGroup.Items.Add("ALL");
//obj.BindToDropDown(DDLCustGroup, queryCustomerGroup, "CUSTGROUP", "CUSTGROUP_CODE");
CreamBell_DMS_WebApps.App_Code.CreamBellFramework.BindToDropDown(DDLCustGroup, queryCustomerGroup, "CUSTGROUP", "CUSTGROUP_CODE", "ALL");
string query = "select bm.bu_code,bu_desc from ax.acxsitebumapping sbp join ax.ACXBUMASTER bm on bm.bu_code = sbp.BU_CODE where SITEID = '" + Convert.ToString(Session["SiteCode"]) + "'";
//DDLBusinessUnit.Items.Clear();
//DDLBusinessUnit.Items.Add("All...");
//obj.BindToDropDown(DDLBusinessUnit, query, "bu_desc", "bu_code");
CreamBell_DMS_WebApps.App_Code.CreamBellFramework.BindToDropDown(DDLBusinessUnit, query, "bu_desc", "bu_code", "All...");
}
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);
}
}
private void ExportToExcelNew()
{
string tempXlsxFilePath = string.Empty;
string tempXlsbFilePath = string.Empty;
try
{
bool b = ValidateInput();
if (b == true)
{
GridView gvvc = new GridView();
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
if (lstState.SelectedValue == string.Empty)
{
string message = "alert('Please Select The SiteID !');";
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", message, true);
}
string siteid, StateCode;
string FromDate = txtFromDate.Text;
string ToDate = txtToDate.Text;
siteid = StateCode = "";
foreach (System.Web.UI.WebControls.ListItem litem in lstState.Items)
{
if (litem.Selected)
{
if (StateCode.Length == 0)
{
StateCode = "" + litem.Value.ToString() + "";
}
else
{
StateCode += "," + litem.Value.ToString() + "";
}
}
}
foreach (System.Web.UI.WebControls.ListItem litem in lstSiteId.Items)
{
if (litem.Selected)
{
if (siteid.Length == 0)
{
siteid = "" + litem.Value.ToString() + "";
}
else
{
siteid += "," + litem.Value.ToString() + "";
}
}
}
string query = "EXEC ACX_USP_OpenSaleOrder '" + StateCode + "','" + siteid.ToString() + "','" + Session["DATAAREAID"].ToString() + "','" + FromDate + "','" + ToDate + "','" + Convert.ToString(Session["USERID"]) + "','" + Convert.ToString(Session["LOGINTYPE"]) + "'";
//string query = "EXEC ACX_USP_OpenSaleOrder '" + StateCode + "','" + siteid.ToString() + "','" + Session["DATAAREAID"].ToString() + "','" + FromDate + "','" + ToDate + "'";
using (SqlCommand cmd = new SqlCommand(query, obj.GetConnection()))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
//cmd.Connection = obj.GetConnection();
cmd.CommandTimeout = 3600;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "Customers");
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
if ("XLSX".CompareTo(rdbListExcelFileFormat.SelectedValue) == 0)
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //xlsx
Response.AddHeader("content-disposition", "attachment;filename=SaleOrderDetailsReport.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
}
}
if ("XLS".CompareTo(rdbListExcelFileFormat.SelectedValue) == 0)
{
Response.ContentType = "application/vnd.ms-excel"; // xls
Response.AddHeader("content-disposition", "attachment;filename=SaleOrderDetailsReport.xls");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
}
}
if ("XLSB".CompareTo(rdbListExcelFileFormat.SelectedValue) == 0)
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //xlsb
Response.AddHeader("content-disposition", "attachment;filename=SaleOrderDetailsReport.xlsb");
var tempFile = DateTime.Now.Ticks.ToString();
tempXlsxFilePath = Server.MapPath("xlfiles/tempXlsxFile_" + tempFile + ".xlsx");
tempXlsbFilePath = Server.MapPath("xlfiles/tempXlsbFile_" + tempFile + ".xlsb");
//CreamBellFramework.ExportToXLSX(tempXlsbFilePath, dt, "Customers");
//wb.SaveAs(tempXlsxFilePath);
//ConvertToXLSBFile(tempXlsxFilePath, tempXlsbFilePath);
Response.WriteFile(tempXlsbFilePath);
}
}
}
}
}
Response.Flush();
Response.End();
}
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
CleanFiles(tempXlsxFilePath, tempXlsbFilePath);
}
finally
{
CleanFiles(tempXlsxFilePath, tempXlsbFilePath);
}
}
private void ShowData_ForExcel()
{
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
string FilterQuery = string.Empty;
SqlConnection conn = null;
SqlCommand cmd = null;
string query = string.Empty;
bool endRequest = false;
try
{
conn = new SqlConnection(obj.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.Text;
if (rdWorking.Checked)
{
query = "GetDataForUseNonuseReport '" + txtFromDate.Text + "','" + txtToDate.Text + "','" + ddlstate.SelectedValue + "','" + rdWorking.Text + "'";
}
else if (rdNotWorking.Checked)
{
query = "GetDataForUseNonuseReport '" + txtFromDate.Text + "','" + txtToDate.Text + "','" + ddlstate.SelectedValue + "','" + rdNotWorking.Text + "'";
}
else
{
query = "GetDataForUseNonuseReport '" + txtFromDate.Text + "','" + txtToDate.Text + "','" + ddlstate.SelectedValue + "'";
}
cmd.CommandText = query;
//cmd.Parameters.AddWithValue("@SITEID", Session["SiteCode"].ToString());
//cmd.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
DataTable dt = new DataTable();
dt = new DataTable();
dt.Load(cmd.ExecuteReader());
//=================Create Excel==========
//var workbook = new ExcelFile();
//// Add new worksheet to Excel file.
//var worksheet = workbook.Worksheets.Add("New worksheet");
//// Set the value of the cell "A1".
//worksheet.Cells["A1"].Value = "Hello world!";
//// Save Excel file.
//workbook.Save("Workbook.xls");
string attachment = "attachment; filename=Usenousereport.xls";
Response.ClearContent();
StringWriter strwritter = new StringWriter();
HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", attachment);
string tab = "";
foreach (DataColumn dc in dt.Columns)
{
Response.Write(tab + dc.ColumnName);
tab = "\t";
}
Response.Write("\n");
int i;
foreach (DataRow dr in dt.Rows)
{
tab = "";
for (i = 0; i < dt.Columns.Count; i++)
{
Response.Write(tab + dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.End();
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
if (conn != null)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
}
}
private DataTable GetData(string strProcName, string strReportName, CreamBell_DMS_WebApps.App_Code.Global obj, ref DataTable dtSetHeader)
{
string query1 = "Select NAME from ax.inventsite where SITEID='" + Session["SiteCode"].ToString() + "'";
dtSetHeader = new DataTable();
dtSetHeader = obj.GetData(query1);
SqlConnection conn = null;
SqlCommand cmd = null;
DataTable dtDataByfilter = null;
string query = string.Empty;
conn = new SqlConnection(obj.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 3600;
cmd.CommandType = CommandType.StoredProcedure;
query = strProcName;
cmd.CommandText = query;
DateTime now = Convert.ToDateTime(txtToDate.Text);
now = now.AddMonths(1);
cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(txtFromDate.Text));
cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text));
string SiteList = "";
foreach (ListItem item in chkListSite.Items)
{
if (item.Selected)
{
if (SiteList == "")
{
SiteList += "" + item.Value.ToString() + "";
}
else
{
SiteList += "," + item.Value.ToString() + "";
}
}
}
if (SiteList.Length > 0)
{
cmd.Parameters.AddWithValue("@SITEID", SiteList);
}
else
{
cmd.Parameters.AddWithValue("@SITEID", "");
}
string strStateCode = "";
foreach (ListItem item in chkListState.Items)
{
if (item.Selected)
{
if (strStateCode == "")
{
strStateCode += "" + item.Value.ToString() + "";
}
else
{
strStateCode += "," + item.Value.ToString() + "";
}
}
}
if (strStateCode.Length > 0)
{
cmd.Parameters.AddWithValue("@STATECODE", strStateCode);
}
else
{
cmd.Parameters.AddWithValue("@STATECODE", "");
}
cmd.Parameters.Add("@SupplyCode", SqlDbType.NVarChar, 40000).Direction = ParameterDirection.Output;
dtDataByfilter = new DataTable();
dtDataByfilter.Load(cmd.ExecuteReader());
DataTable dt = new DataTable();
dt = dtDataByfilter;
SupplierCode = Convert.ToString(cmd.Parameters["@SupplyCode"].Value);
return(dt);
}
