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");
chkListState.Items.Clear();
DataRow dr = dtState.NewRow();
//dr[0] = "--Select--";
//dr[1] = "--Select--";
dtState.Columns.Add("Name", typeof(string), "STATE + ' - ' + STATENAME");
DataView dv = dtState.DefaultView;
dv.Sort = "STATENAME ASC";
//dtState.Rows.InsertAt(dr, 0);
chkListState.DataSource = dv;
chkListState.DataTextField = "NAME";
chkListState.DataValueField = "STATE";
chkListState.DataBind();
}
else
{
sqlstr = "Select Distinct I.StateCode Code,I.StateCode + ' - ' +LS.Name as Name,LS.Name as StateName 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 StateName";
DataTable dt2 = new DataTable();
SqlCommand cmd1 = new SqlCommand();
cmd1.Connection = baseObj.GetConnection();
cmd1.CommandText = sqlstr;
SqlDataAdapter sda = new SqlDataAdapter(cmd1);
sda.Fill(dt2);
//chkListState.Items.Add("All...");
chkListState.DataSource = dt2;
chkListState.DataTextField = "NAME";
chkListState.DataValueField = "Code";
chkListState.DataBind();
}
}
else
{
sqlstr = "Select Distinct I.StateCode StateCode,I.StateCode + ' - ' + LS.Name as StateName,LS.Name as Name from [ax].[INVENTSITE] I left join [ax].[LOGISTICSADDRESSSTATE] LS on LS.STATEID = I.STATECODE where I.STATECODE <>'' ORDER BY Name";
chkListState.Items.Add("Select...");
dt = baseObj.GetData(sqlstr);
chkListState.DataSource = dt;
chkListState.DataTextField = "StateName";
chkListState.DataValueField = "StateCode";
chkListState.DataBind();
}
if (chkListState.Items.Count == 1)
{
chkListState.Items[0].Selected = true;
chkListState_SelectedIndexChanged(null, null);
}
}
private void ShowReport()
{
try
{
conn = Obj.GetConnection();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = string.Empty;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ACX_PSRORDERDETAILS";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@SITECODE", Session["USERID"].ToString());
cmd.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
cmd.Parameters.AddWithValue("@STARTDATE", this.txtFromDate.Text);
cmd.Parameters.AddWithValue("@ENDDATE", this.txtToDate.Text);
cmd.Parameters.AddWithValue("@PSRCODE", this.DrpPSRDetails.SelectedValue.Trim().ToString());
if (DrpProductGrp.Text == "Select...")
{
cmd.Parameters.AddWithValue("@PRODUCTGROUP", string.Empty);
}
else
{
cmd.Parameters.AddWithValue("@PRODUCTGROUP", this.DrpProductGrp.Text.Trim().ToString());
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
dtData = new DataTable();
da.Fill(dtData);
if (dtData.Rows.Count > 0)
{
LoadDataInReportViewer(dtData);
}
else
{
LblMessage.Text = "► No Records Exist !!";
}
}
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);
}
}
protected void btnReport_Click(object sender, EventArgs e)
{
if (txtFromDate.Text == "")
{
string message = "alert('From Date cannot be empty');";
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", message, true);
return;
}
if (txtToDate.Text == "")
{
string message = "alert('To Date cannot be empty');";
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", message, true);
return;
}
int result = DateTime.Compare(Convert.ToDateTime(txtFromDate.Text), DateTime.Now.Date);
if (result != 0 && result > 0)
{
string message = "alert('From Date cannot be greater than current date');";
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", message, true);
return;
}
int result1 = DateTime.Compare(Convert.ToDateTime(txtToDate.Text), DateTime.Now.Date);
if (result1 != 0 && result1 > 0)
{
string message = "alert('To Date cannot be greater than current date');";
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", message, true);
return;
}
int result2 = DateTime.Compare(Convert.ToDateTime(txtFromDate.Text), Convert.ToDateTime(txtToDate.Text));
if (result2 != 0 && result2 > 0)
{
string message = "alert('From Date cannot be greater than To Date');";
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", message, true);
return;
}
SqlConnection con = baseObj.GetConnection();
string query = string.Empty;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
cmd.CommandTimeout = 0;
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());
gvDetails.DataSource = dt;
gvDetails.DataBind();
gvDetails.Visible = true;
//cmd.CommandText = "exec GetDataForUseNonuseReport '" + txtFromDate.Text + "','" + txtToDate.Text + "','" + ddlstate.SelectedValue + "'";
//DataTable dt = new DataTable();
// dt.Load(cmd.ExecuteReader());
//if (rdWorking.Checked == true)
//{
// gvDetails.DataSource = dt.Select("[status]='Working'").CopyToDataTable();
//}
//else if (rdNotWorking.Checked == true)
//{
// gvDetails.DataSource = dt.Select("[status]='Not Working'").CopyToDataTable();
//}
//else
//{
// gvDetails.DataSource = dt;
//}
//gvDetails.DataBind();
//gvDetails.Visible = true;
}