public void FillGrid()
{
try
{
string query = "";
dt_ = new DataTable();
query = " select distinct(convert(date, cast(dsrdat as date) ,103)) as [dsrdat],username,Isdon " +
" from tbl_Mdsr where tbl_Mdsr.CompanyId = '" + Session["CompanyID"] + "' and tbl_Mdsr.BranchId ='" + Session["BranchID"] + "' order by " +
" convert(date, cast(dsrdat as date) ,105) desc";
SqlCommand cmd = new SqlCommand(query, con);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt_);
if (dt_.Rows.Count > 0)
{
GVDSR.DataSource = dt_;
GVDSR.DataBind();
ViewState["vdsr"] = dt_;
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void LinkBtnExportExcel_Click(object sender, EventArgs e)
{
try
{
System.Threading.Thread.Sleep(2000);
Response.Clear();
Response.Buffer = true;
Response.ClearContent();
Response.ClearHeaders();
Response.Charset = "";
string FileName = "DailySalesReport_" + DSRFDat + '_' + DSRTDat + ".xls";
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;filename=" + FileName);
GVDSR.GridLines = GridLines.Both;
GVDSR.HeaderStyle.Font.Bold = true;
GVDSR.RenderControl(htmltextwrtter);
Response.Write(strwritter.ToString());
Response.End();
}
catch (Exception ex)
{
throw;
//ScriptManager.RegisterStartupScript(this, this.GetType(), "isActive", "Alert();", true);
//lblalert.Text = ex.Message;
}
}
public void FillGrid()
{
try
{
DSR = Request.QueryString["ID"];
DAT = Request.QueryString["DAT"];
Cust = Request.QueryString["Cust"];
Usr = Request.QueryString["Usr"];
dt_ = new DataTable();
/*if (DAT != null && Cust != "0" && Usr != null)
* {
* dt_ = DBConnection.GetQueryData(" select * from v_dsr where CompanyId = '" + Session["CompanyID"] + "' and BranchId= '" + Session["BranchID"] + "' and MSal_dat='" + DAT + "' and CustomerID='" + Cust + "' and username='******'");
*
* }
* else if (DAT != null && Cust != "0")
* {
* dt_ = DBConnection.GetQueryData(" select * from v_dsr where CompanyId = '" + Session["CompanyID"] + "' and BranchId= '" + Session["BranchID"] + "' and MSal_dat='" + DAT + "' and CustomerID='" + Cust + "'");
*
* }else if (DAT != null && Usr!= null)
* {
* dt_ = DBConnection.GetQueryData(" select * from v_dsr where CompanyId = '" + Session["CompanyID"] + "' and BranchId= '" + Session["BranchID"] + "' and MSal_dat='" + DAT + "' and username='******'");
*
* }
* else if (Cust != "0" && Usr != null)
* {
* dt_ = DBConnection.GetQueryData(" select * from v_dsr where CompanyId = '" + Session["CompanyID"] + "' and BranchId= '" + Session["BranchID"] + "' and CustomerID='" + Cust + "' and username='******'");
*
* }
* else if (DAT != null)
* {
* dt_ = DBConnection.GetQueryData(" select * from v_dsr where CompanyId = '" + Session["CompanyID"] + "' and BranchId= '" + Session["BranchID"] + "' and MSal_dat='" + DAT + "'");
* //select * from v_dsr where v_dsr.MSal_dat='01/17/2019' and BranchId= '001' and CompanyId = 'COM_001'
* }
* else if(Cust != null)
* {
* dt_ = DBConnection.GetQueryData(" select * from v_dsr where CompanyId = '" + Session["CompanyID"] + "' and BranchId= '" + Session["BranchID"] + "' and CustomerID='" + Cust + "'");
* //select * from v_dsr where v_dsr.MSal_dat='01/17/2019' and BranchId= '001' and CompanyId = 'COM_001'
* }
* else*/
//if (Usr != null)
// {
// //dt_ = DBConnection.GetQueryData(" select * from view_dsr where CompanyId = '" + Session["CompanyID"] + "' and BranchId= '" + Session["BranchID"] + "' and username='******'");
// //select * from v_dsr where v_dsr.MSal_dat='01/17/2019' and BranchId= '001' and CompanyId = 'COM_001'
// }
//else
{
dt_ = DBConnection.GetQueryData(" select * from view_dsr where CompanyId = '" + Session["CompanyID"] + "' and BranchId= '" + Session["BranchID"] + "'");// and MSal_dat=replace(convert(NVARCHAR, getdate(), 106), ' ', '/')");
}
GVDSR.DataSource = dt_;
GVDSR.DataBind();
}
catch (Exception ex)
{
throw ex;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
try
{
lblcom.Text = "NM Garments";
DSRID = Request.QueryString["DSRID"];
EID = Request.QueryString["EID"];
DSRFDat = Request.QueryString["DSRFDat"];
DSRTDat = Request.QueryString["DSRTDat"];
getbranchData();
lbl_dat.Text = DSRFDat + " to " + DSRTDat;
if (con.State != ConnectionState.Open)
{
con.Close();
}
con.Open();
SqlDataAdapter da = new SqlDataAdapter(" select distinct(Adv) as Adv ,ttl as Amt,tbl_DCPos.bal as bal,RecieverNam,tbl_MCPos.BillNo from tbl_MCPos inner join tbl_DCPos on tbl_MCPos.MCposid = tbl_DCPos.MCposid " +
" inner join Products on tbl_DCPos.ProductID = Products.ProductID where billdat between'" + DSRFDat + "' and '" + DSRTDat + "' and Products.CompanyId='" + Session["CompanyID"] + "' and Products.BranchId='" + Session["BranchID"] + "' and Iscancel <> 1", con);
DataSet ds = new DataSet();
da.Fill(ds, "tbl_MCPos");
if (ds.Tables["tbl_MCPos"].Rows.Count > 0)
{
Amt = ds.Tables["tbl_MCPos"].Rows[0]["Amt"].ToString();
//dis = ds.Tables["tbl_MCPos"].Rows[0]["dis"].ToString();
//if (dis != "0" || dis != "" || dis != "NULL")
//{
//}
GVDSR.DataSource = ds.Tables[0];
GVDSR.DataBind();
}
con.Close();
}
catch (Exception ex)
{
throw ex;
}
}
}
public void getdsr(string dsrid)
{
try
{
string query = " select tbl_Mdsr.dsrid,ROW_NUMBER() OVER(ORDER BY tbl_Mdsr.dsrid DESC) AS [ID],tbl_Mdsr.CustomerID,CustomerName,ProductName,salrat,(salrat * Qty) as [Sale Rate],Qty as [Qty],tbl_ddsr.outstan, '' as [Return], " +
" dsrrmk,tbl_Mdsr.CreateBy,salrturn,username,recvry, replace (convert(NVARCHAR, dsrdat, 101), '/', '/') as [dsrdat],tbl_Mdsr.CompanyId,tbl_Mdsr.BranchId " +
" from tbl_Mdsr inner join tbl_ddsr on tbl_Mdsr.dsrid = tbl_ddsr.dsrid " +
" inner join Customers_ on tbl_Mdsr.CustomerID = Customers_.CustomerID " +
" inner join Products on tbl_ddsr.ProductID = Products.ProductID where tbl_Mdsr.dsrid= '" + dsrid + "' and tbl_Mdsr.CompanyId = '" + Session["CompanyID"] + "' and tbl_Mdsr.BranchId= '" + Session["BranchID"] + "'";
dt_ = DBConnection.GetQueryData(query);
GVDSR.DataSource = dt_;
GVDSR.DataBind();
}catch (Exception ex)
{
throw ex;
}
}
private void SearchRecord()
{
try
{
FillGrid();
DataTable _dt = (DataTable)ViewState["vdsr"];
DataView dv = new DataView(_dt, "Username LIKE '%" + TBSDSR.Text.Trim().ToUpper() + "%'", "[Username] ASC", DataViewRowState.CurrentRows);
DataTable dt_ = new DataTable();
dt_ = dv.ToTable();
GVDSR.DataSource = dt_;
GVDSR.DataBind();
ViewState["vdsr"] = dt_;
}
catch (Exception ex)
{
throw;
//ScriptManager.RegisterStartupScript(this, this.GetType(), "isActive", "Alert();", true);
//lblalert.Text = ex.Message;
}
}
public void FillGrid()
{
try
{
//Sales Order
using (SqlCommand cmd = new SqlCommand())
{
string lvl = Session["Level"].ToString();
if (lvl == "1")
{
cmd.CommandText = " select tbl_Mdsr.CustomerID,Isdon,CustomerName,convert(varchar, dsrdat, 111) as [dsrdat],tbl_Mdsr.dsrid from tbl_Mdsr inner join Customers_ on tbl_Mdsr.CustomerID = Customers_.CustomerID where tbl_Mdsr.CompanyId = '" + Session["CompanyID"] + "' and tbl_Mdsr.BranchId= '" + Session["BranchID"] + "' order by dsrid desc ";
}
else
{
cmd.CommandText = " select tbl_Mdsr.CustomerID,Isdon,CustomerName,convert(varchar, dsrdat, 111) as [dsrdat],tbl_Mdsr.dsrid from tbl_Mdsr inner join Customers_ on tbl_Mdsr.CustomerID = Customers_.CustomerID where tbl_Mdsr.CompanyId = '" + Session["CompanyID"] + "' and tbl_Mdsr.BranchId= '" + Session["BranchID"] + "' order by dsrid desc ";
}
cmd.Connection = con;
con.Open();
DataTable dtSal_ = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dtSal_);
GVDSR.DataSource = dtSal_;
GVDSR.DataBind();
con.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
