protected void fillSchemeode()
{
DataTable dt = new DataTable();
chkScheme.Items.Clear();
string sqlstr = string.Empty;
sqlstr = "Select Distinct Schemecode,[Scheme Description] AS SchemeName From ACXAllSCHEMEVIEW Where ((StartingDate between '" + txtFromDate.Text.ToString() + "' And '" + txtToDate.Text.ToString() + "') or (ENDINGDATE between '" + txtFromDate.Text.ToString() + "' and '" + txtToDate.Text.ToString() + "')) ";
//((StartingDate<='01-Feb-2017' and ENDINGDATE>='01-Feb-2017') Or (StartingDate<='28-Feb-2017' and ENDINGDATE>='28-Feb-2017'))
if (ddlState.SelectedIndex > 0 && ddlSiteId.SelectedIndex < 0)
{
sqlstr += " AND ([Sales Type]='ALL' OR ([Sales Type] IN ('State','SITE') and ([SalesCode] in ('" + ddlState.SelectedValue.ToString() + "') OR [SalesCode] in (SELECT SITEID FROM AX.INVENTSITE WHERE STATECODE='" + ddlState.SelectedValue.ToString() + "'))))";
}
if (ddlSiteId.SelectedIndex > 0)
{
sqlstr += " AND ([Sales Type]='ALL' OR ([Sales Type]='State' and [SalesCode]='" + ddlState.SelectedValue.ToString() + "') OR ([Sales Type]='SITE' AND [SalesCode]='" + ddlSiteId.SelectedValue.ToString() + "'))";
}
else if (Convert.ToString(Session["ISDISTRIBUTOR"]) == "Y")
{
sqlstr += " AND ([Sales Type]='ALL' OR ([Sales Type]='State' and [SalesCode]='" + ddlState.SelectedValue.ToString() + "') OR ([Sales Type]='SITE' AND [SalesCode]='" + ddlSiteId.SelectedValue.ToString() + "'))";
}
dt = new DataTable();
dt = baseObj.GetData(sqlstr);
chkScheme.Items.Clear();
//for (int i = 0; i < dt.Rows.Count; i++)
//{
chkScheme.DataSource = dt;
chkScheme.DataTextField = "SchemeName";
chkScheme.DataValueField = "Schemecode";
chkScheme.DataBind();
Scheme.Update();
// }
}
protected void Button2_Click(object sender, EventArgs e)
{
string multipleparameter = string.Empty;
int count = 0;
foreach (GridViewRow grv in GridView1.Rows)
{
CheckBox chklist1 = (CheckBox)grv.Cells[0].FindControl("chklist");
LinkButton lnkBtn = (LinkButton)grv.Cells[0].FindControl("lnkbtn");
if (chklist1.Checked)
{
count += 1;
// ShowReportSaleInvoice(string.Empty, chklist1.Text);
if (multipleparameter == string.Empty)
{
multipleparameter = "'" + lnkBtn.Text + "'";
}
else
{
multipleparameter += ",'" + lnkBtn.Text + "'";
}
}
}
if (count == 0)
{
return;
}
string listLoadsheet = "select * from ax.ACXLOADSHEETHEADER where SITEID = '" + Session["SiteCode"].ToString() + "' and LOADSHEET_NO in (" + multipleparameter + ") order by LOADSHEET_NO";
DataTable dtMainHeader1 = new DataTable();
dtMainHeader1 = obj.GetData(listLoadsheet);
for (int i = 0; i < dtMainHeader1.Rows.Count; i++)
{
//==============================
string queryHeader = "select LSH.LOADSHEET_NO, CONVERT(varchar(15),LSH.LOADSHEET_DATE,105) AS LOADSHEET_DATE , " +
" ( SH.CUSTOMER_CODE +'-' + CUS.CUSTOMER_NAME) as CUSTOMER, SH.SO_NO, CONVERT( varchar(15), SH.SO_DATE,105) AS SO_DATE , " +
" cast(round(SH.SO_VALUE,2) as numeric(36,2)) as SO_VALUE, SH.SITEID, USM.User_Name,USM.State from ax.ACXLOADSHEETHEADER LSH " +
" INNER JOIN ax.ACXSALESHEADER SH ON LSH.LOADSHEET_NO = SH.LoadSheet_No and LSH.SITEID= SH.SITEID " +
" INNER JOIN AX.ACXCUSTMASTER CUS ON SH.CUSTOMER_CODE = CUS.CUSTOMER_CODE " +// exclude on 4th Apr 2017 and cus.Site_Code = LSH.SITEID" +
" INNER JOIN AX.ACXUSERMASTER USM ON LSH.SITEID= USM.Site_Code " +
" where LSH.SITEID = '" + Session["SiteCode"].ToString() + "' and LSH.LOADSHEET_NO='" + dtMainHeader1.Rows[i]["LOADSHEET_NO"].ToString() + "' order by LSH.LOADSHEET_NO";
dtHeader = obj.GetData(queryHeader);
string queryLine = " Select ROW_NUMBER() over (ORDER BY LOADSHEET_NO,LSH.PRODUCT_CODE) AS SRNO,LOADSHEET_NO,( LSH.PRODUCT_CODE + '-'+ PM.PRODUCT_NAME) AS PRODUCT, "
+ " LSH.BOXQTY as BOX,LSH.PCSQTY as PCS,isnull(LSH.BOXPCS,0) as TotalBoxPCS,LSH.BOX as TotalQtyConv,LSH.LTR, LSH.STOCKQTY_BOX,LSH.STOCKQTY_LTR from ax.ACXLOADSHEETLINE LSH " +
" Inner Join ax.inventtable PM on LSH.PRODUCT_CODE = PM.ITEMID " +
" where SHITEID = '" + Session["SiteCode"].ToString() + "' and LOADSHEET_NO='" + dtMainHeader1.Rows[i]["LOADSHEET_NO"].ToString() + "'";
dtLinetest = obj.GetData(queryLine);
ShowReportLoadSheet(dtHeader, dtLinetest);
}
murgebytges();
}
protected void GetVRSName()
{
string SiteList = "";
if (Convert.ToString(Session["ISDISTRIBUTOR"]) == "Y")
{
SiteList = "'" + Convert.ToString(Session["SiteCode"]) + "'";
}
else
{
foreach (ListItem item in ddlSiteId.Items)
{
if (item.Selected)
{
if (SiteList == "")
{
SiteList += "'" + item.Value.ToString() + "'";
}
else
{
SiteList += ",'" + item.Value.ToString() + "'";
}
}
}
}
if (SiteList.Length > 0)
{
DataTable dt = new DataTable();
string sqlstr1 = string.Empty;
string sqlstr = @"SELECT DISTINCT ax.ACXCUSTMASTER.CUSTOMER_CODE , ax.ACXCUSTMASTER.CUSTOMER_NAME FROM ax.INVENTSITE INNER JOIN
ax.ACXCUSTMASTER ON ax.INVENTSITE.SITEID = ax.ACXCUSTMASTER.SITE_CODE INNER JOIN
ax.ACXCUSTGROUPMASTER ON ax.ACXCUSTMASTER.CUST_GROUP = ax.ACXCUSTGROUPMASTER.CUSTGROUP_CODE where
ax.INVENTSITE.STATECODE <>'' and [ax].[ACXCUSTMASTER].CUst_Group='CG0002' and ax.INVENTSITE.SITEID in (" + SiteList + ") ";
dt = baseObj.GetData(sqlstr);
drpVRS.Items.Clear();
drpVRS.Items.Add("Select...");
baseObj.BindToDropDown(drpVRS, sqlstr, "CUSTOMER_NAME", "CUSTOMER_CODE");
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// drpVRS.DataSource = dt;
// drpVRS.DataTextField = "CUSTOMER_NAME";
// drpVRS.DataValueField = "CUSTOMER_CODE";
// drpVRS.DataBind();
//}
}
else
{
drpVRS.Items.Clear();
}
}
protected void lnkbtn_Click(object sender, EventArgs e)
{
try
{
//GridViewRow row = (GridViewRow)((LinkButton)sender).NamingContainer ;
LinkButton lnkbtn = (LinkButton)sender;
string sqlstr = "Select ITEMID,[GROUP],[ITEMNAME],[GROUPNAME] from [ax].[ACXFREEITEMGROUPTABLE] where [Group] = '" + lnkbtn.Text + "'";
DataTable dtCustomer = obj.GetData(sqlstr);
gridView1.DataSource = dtCustomer;
gridView1.DataBind();
}
catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); }
}
protected void FillIndentList()
{
string strSQL = string.Format("EXEC Ax.ACX_IndentToSO '{0}','{1}'", Session["USERID"].ToString(), Session["DATAAREAID"].ToString());
gvDetails.DataSource = null;
using (DataTable dt = baseObj.GetData(strSQL))
{
if (dt.Rows.Count > 0)
{
gvDetails.DataSource = dt;
}
}
gvDetails.DataBind();
}
private void FillDdlList()
{
string query = string.Empty;
DataTable dt = new DataTable();
query = @"SELECT VersionName ,VersionName +'-'+ Description VersionInfo FROM ACX_VW_GetVersionInfo Where IsConfirm='Yes' Order By VersionName";
ddlVersionNew.Items.Clear();
ddlVersionNew.Items.Add("Select...");
baseobj.BindToDropDownp(ddlVersionNew, query, "VersionInfo", "VersionName");
ddlVersionCodeNew.Items.Clear();
query = @"SELECT 'Un-Assigned' AS VersionName UNION SELECT VersionName FROM ACX_VW_GetVersionInfo Order By VersionName";
dt = baseobj.GetData(query);
ddlVersionCodeNew.DataSource = dt;
ddlVersionCodeNew.DataTextField = "VersionName";
ddlVersionCodeNew.DataValueField = "VersionName";
ddlVersionCodeNew.DataBind();
//baseobj.BindToDropDown(ddlVersionCode, query, "VersionName", "VersionName");
query = @"SELECT STATEID,NAME FROM AX.LOGISTICSADDRESSSTATE ORDER BY NAME";
//baseobj.BindToDropDown(ddlState, query, "Name", "STATEID");
ddlStateNew.Items.Clear();
dt = baseobj.GetData(query);
ddlStateNew.DataSource = dt;
ddlStateNew.DataTextField = "Name";
ddlStateNew.DataValueField = "STATEID";
ddlStateNew.DataBind();
query = @"SELECT 'VRS' AS NAME UNION SELECT 'PSR' AS NAME ORDER BY NAME";
//baseobj.BindToDropDown(ddlUserType, query, "Name", "ID");
ddlUserTypeNew.Items.Clear();
dt = baseobj.GetData(query);
ddlUserTypeNew.DataSource = dt;
ddlUserTypeNew.DataTextField = "Name";
ddlUserTypeNew.DataValueField = "Name";
ddlUserTypeNew.DataBind();
query = @"SELECT 'YES' AS NAME UNION SELECT 'NO' AS NAME ORDER BY NAME";
ddlIsBlockNew.Items.Clear();
dt = baseobj.GetData(query);
ddlIsBlockNew.DataSource = dt;
ddlIsBlockNew.DataTextField = "Name";
ddlIsBlockNew.DataValueField = "Name";
ddlIsBlockNew.DataBind();
//baseobj.BindToDropDown(ddlIsBlock, query, "Name", "ID");
}
protected void FillState()
{
try {
string sqlstr = "select * from ax.ACXSITEMENU where SITE_CODE ='" + Session["SiteCode"].ToString() + "'";
object objcheckSitecode = baseObj.GetScalarValue(sqlstr);
DataTable dt = new DataTable();
dt = new DataTable();
if (objcheckSitecode != null)
{
chkListState.Items.Clear();
string sqlstr11 = "Select Distinct I.StateCode Code,LS.Name from [ax].[INVENTSITE] I left join [ax].[LOGISTICSADDRESSSTATE] LS on LS.STATEID = I.STATECODE where I.STATECODE <>'' ";
dt = baseObj.GetData(sqlstr11);
chkListState.Items.Add("All...");
for (int i = 0; i < dt.Rows.Count; i++)
{
chkListState.DataSource = dt;
chkListState.DataTextField = "NAME";
chkListState.DataValueField = "Code";
chkListState.DataBind();
}
}
else
{
chkListState.Items.Clear();
chkListSite.Items.Clear();
string sqlstr1 = @"Select I.StateCode StateCode,LS.Name as StateName,I.SiteId,I.Name as SiteName from [ax].[INVENTSITE] I left join [ax].[LOGISTICSADDRESSSTATE] LS on LS.STATEID = I.STATECODE where I.SiteId = '" + Session["SiteCode"].ToString() + "'";
dt = baseObj.GetData(sqlstr1);
for (int i = 0; i < dt.Rows.Count; i++)
{
chkListState.DataSource = dt;
chkListState.DataTextField = "StateName";
chkListState.DataValueField = "StateCode";
chkListState.DataBind();
chkListSite.DataSource = dt;
chkListSite.DataTextField = "SiteName";
chkListSite.DataValueField = "SiteId";
chkListSite.DataBind();
}
chkListState.Items[0].Selected = true;
chkListSite.Items[0].Selected = true;
FillCustomerGroup();
}
}
catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); }
}
protected void FillState()
{
string sqlstr = "select * from ax.ACXSITEMENU where SITE_CODE IN (" + ucRoleFilters.GetCommaSepartedSiteId() + ")";
object objcheckSitecode = baseObj.GetScalarValue(sqlstr);
DataTable dt = new DataTable();
dt = new DataTable();
if (objcheckSitecode != null)
{
chkListState.Items.Clear();
string sqlstr11 = "Select Distinct I.StateCode Code,LS.Name from [ax].[INVENTSITE] I left join [ax].[LOGISTICSADDRESSSTATE] LS on LS.STATEID = I.STATECODE where I.STATECODE <>'' ";
dt = baseObj.GetData(sqlstr11);
chkListState.Items.Add("All...");
for (int i = 0; i < dt.Rows.Count; i++)
{
chkListState.DataSource = dt;
chkListState.DataTextField = "NAME";
chkListState.DataValueField = "Code";
chkListState.DataBind();
}
}
else
{
chkListState.Items.Clear();
chkListSite.Items.Clear();
string sqlstr1 = @"Select I.StateCode StateCode,LS.Name as StateName,I.SiteId,I.Name as SiteName from [ax].[INVENTSITE] I left join [ax].[LOGISTICSADDRESSSTATE] LS on LS.STATEID = I.STATECODE where I.SiteId IN (" + ucRoleFilters.GetCommaSepartedSiteId() + ")";
dt = baseObj.GetData(sqlstr1);
for (int i = 0; i < dt.Rows.Count; i++)
{
chkListState.DataSource = dt;
chkListState.DataTextField = "StateName";
chkListState.DataValueField = "StateCode";
chkListState.DataBind();
chkListSite.DataSource = dt;
chkListSite.DataTextField = "SiteName";
chkListSite.DataValueField = "SiteId";
chkListSite.DataBind();
}
if (chkListSite.Items.Count > 1)
{
chkListState.Items[0].Selected = true;
chkListSite.Items[0].Selected = true;
}
FillCustomerGroup();
}
}
private void FillDdlList()
{
string query = string.Empty;
DataTable dt = new DataTable();
//ddlVersionCode.Items.Clear();
query = @"SELECT 'Un-Assigned' AS VersionName UNION SELECT VersionName FROM ACX_VW_GetVersionInfo Order By VersionName";
dt = baseobj.GetData(query);
chkListVersion.Items.Clear();
chkListVersion.Items.Add("All...");
chkListVersion.DataSource = dt;
chkListVersion.DataTextField = "VersionName";
chkListVersion.DataValueField = "VersionName";
chkListVersion.DataBind();
query = @"SELECT STATEID,NAME FROM AX.LOGISTICSADDRESSSTATE ORDER BY NAME";
//baseobj.BindToDropDown(ddlState, query, "Name", "STATEID");
dt = baseobj.GetData(query);
chkListState.Items.Clear();
chkListState.Items.Add("All...");
chkListState.DataSource = dt;
chkListState.DataTextField = "NAME";
chkListState.DataValueField = "STATEID";
chkListState.DataBind();
query = @"SELECT 'VRS' AS NAME UNION SELECT 'PSR' AS NAME ORDER BY NAME";
dt = baseobj.GetData(query);
chkListUserType.Items.Clear();
chkListUserType.Items.Add("All...");
chkListUserType.DataSource = dt;
chkListUserType.DataTextField = "NAME";
chkListUserType.DataValueField = "NAME";
chkListUserType.DataBind();
query = @"SELECT 'YES' AS NAME UNION SELECT 'NO' AS NAME ORDER BY NAME";
dt = baseobj.GetData(query);
chkListBlock.Items.Clear();
chkListBlock.Items.Add("All...");
chkListBlock.DataSource = dt;
chkListBlock.DataTextField = "NAME";
chkListBlock.DataValueField = "NAME";
chkListBlock.DataBind();
}
protected void FillTemplate()
{
try
{
DataTable dt = new DataTable();
string sqlstr11 = "select * from [ax].[ACX_TemplateInfo] ";
dt = baseObj.GetData(sqlstr11);
ddlTemplate.DataSource = dt;
ddlTemplate.DataTextField = "TemplateName";
ddlTemplate.DataValueField = "TemplateCode";
ddlTemplate.DataBind();
}
catch (Exception)
{
throw;
}
}
protected void DrpItemDescription_SelectedIndexChanged(object sender, EventArgs e)
{
txtRemark.Text = string.Empty;
txtQTYPcs.Text = string.Empty;
//===========Fill Product Group and Product Sub Cat========
DataTable dt = new DataTable();
string query = "select Product_Group,PRODUCT_SUBCATEGORY from ax.INVENTTABLE where ItemId='" + DrpItemDescription.SelectedItem.Value + "' order by Product_Name";
dt = baseObj.GetData(query);
if (dt.Rows.Count > 0)
{
drpItemCategory.Text = dt.Rows[0]["Product_Group"].ToString();
ProductSubCategory();
//=============For Product Sub Cat======
drpItemCode.Text = dt.Rows[0]["PRODUCT_SUBCATEGORY"].ToString();
}
}
//protected void btnSearchCustomer_Click(object sender, EventArgs e)
//{
// if (txtSearch.Text == string.Empty)
// {
// this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Please provide the Searching Keyword !');", true);
// txtSearch.Focus();
// gridViewCustomers.DataSource = ViewState["dtCustomer"];
// gridViewCustomers.DataBind();
// }
// else
// {
// ShowCustomerByFilter();
// }
//}
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");
dtState.Columns.Add("STATENAMES", typeof(string), "STATE + ' - ' + STATENAME");
lstState.Items.Clear();
DataRow dr = dtState.NewRow();
lstState.DataSource = dtState;
lstState.DataTextField = "STATENAMES";
lstState.DataValueField = "STATE";
lstState.DataBind();
}
else
{
sqlstr = "Select Distinct I.StateCode Code,I.StateCode + ' - ' + LS.Name AS Name 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 Name";
//ddlState.Items.Add("Select...");
// baseObj.BindToDropDown(ddlState, sqlstr, "Name", "Code");
DataTable dt1 = baseObj.GetData(sqlstr);
lstState.DataSource = dt1;
lstState.DataTextField = "Name";
lstState.DataValueField = "Code";
lstState.DataBind();
}
}
else
{
sqlstr = "Select Distinct I.StateCode Code,I.StateCode + ' - ' + LS.Name AS Name from [ax].[INVENTSITE] I left join [ax].[LOGISTICSADDRESSSTATE] LS on LS.STATEID = I.STATECODE where I.STATECODE <>'' order by Name ";
lstState.Items.Add("Select...");
// baseObj.BindToDropDown(ddlState, sqlstr, "Name", "Code");
DataTable dt1 = baseObj.GetData(sqlstr);
lstState.DataSource = dt1;
lstState.DataTextField = "Name";
lstState.DataValueField = "Code";
lstState.DataBind();
}
if (lstState.Items.Count == 1)
{
foreach (System.Web.UI.WebControls.ListItem litem in lstState.Items)
{
litem.Selected = true;
}
ddlCountry_SelectedIndexChanged(null, null);
}
}
protected void fillCustomeGroup()
{
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
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);
DataTable distinctValues = view.ToTable(true, "CUSTGROUP", "CUSTGROUPDESC");
string CustList = "";
foreach (DataRow row in distinctValues.Rows)
{
if (CustList == "")
{
CustList += "'" + row["CUSTGROUP"].ToString() + "'";
}
else
{
CustList += ",'" + row["CUSTGROUP"].ToString() + "'";
}
}
string sqlstr1 = @"Select distinct CustGroup_Code + '-' + CustGroup_Name as CustGroup_Name,CustGroup_Code from VW_ReportsCustGroup WHERE MENUCODE='M160' AND CustGroup_Code IN (" + CustList + ") Order BY CustGroup_Code";
DataTable dt1 = baseObj.GetData(sqlstr1);
lstCustGroup.DataSource = dt1;
lstCustGroup.DataTextField = "CustGroup_Name";
lstCustGroup.DataValueField = "CustGroup_Code";
lstCustGroup.DataBind();
}
else
{
lstCustGroup.Items.Clear();
string sqlstr1 = @"Select distinct CustGroup_Code + '-' + CustGroup_Name as CustGroup_Name,CustGroup_Code from VW_ReportsCustGroup WHERE MENUCODE='M160' Order BY CustGroup_Code";
DataTable dt1 = baseObj.GetData(sqlstr1);
lstCustGroup.DataSource = dt1;
lstCustGroup.DataTextField = "CustGroup_Name";
lstCustGroup.DataValueField = "CustGroup_Code";
lstCustGroup.DataBind();
//lstCust.Items.Insert(0, "All...");
}
}
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);
}
protected void Button2_Click(object sender, EventArgs e)
{
string multipleInvoice = string.Empty;
foreach (GridViewRow grv in GridView1.Rows)
{
CheckBox chklist1 = (CheckBox)grv.Cells[0].FindControl("chklist");
LinkButton lnkBtn = (LinkButton)grv.Cells[0].FindControl("lnkbtn");
if (chklist1.Checked)
{
// ShowReportSaleInvoice(string.Empty, chklist1.Text);
if (multipleInvoice == string.Empty)
{
multipleInvoice = "'" + lnkBtn.Text + "'";
}
else
{
multipleInvoice += ",'" + lnkBtn.Text + "'";
}
}
}
if (multipleInvoice == "")
{
LblMessage.Text = "Please select Invoice No.. !";
return;
}
string queryHeader = " Select SHD.INVOICE_NO, IST.ACXADDRESS1 +' '+ACXADDRESS2 AS SITEADDRESS, IST.AcxCity,IST.ACXzipcode,IST.STATECODE AS SITESTATE, IST.ACXVAT AS SITEVAT," +
" IST.ACXMOBILE AS SITEMOBILE,IST.ACXTELEPHONE AS SitePhoneNo, CONVERT(nvarchar(15), SHD.INVOIC_DATE,105) as INVOIC_DATE, SHD.SITEID, " +
" USM.User_Name, USM.State, SHD.CUSTGROUP_CODE,SHD.CUSTOMER_CODE, CUS.CUSTOMER_NAME, CUS.ADDRESS1,CUS.ADDRESS2,CUS.CITY, " +
" CUS.AREA,CUS.DISTRICT, CUS.STATE, CUS.MOBILE_NO, CUS.PHONE_NO,CUS.VAT,SHD.SO_NO, CONVERT(nvarchar(15),SHD.SO_DATE,105) as SO_DATE ," +
" SHD.LOADSHEET_NO, CONVERT(nvarchar(15),SHD.LOADSHEET_DATE,105) as LOADSHEET_DATE ,SHD.TRANSPORTER_CODE, SHD.VEHICAL_NO, " +
" SHD.DRIVER_CODE, SHD.DRIVER_MOBILENO, SHD.INVOICE_VALUE, SHD.LOADSHEETQTY, " +
" CASE WHEN CUS.VAT='' THEN 'RETAIL INVOICE' ELSE 'TAX INVOICE' END AS REPORTTYPE , Remark" +
" from ax.ACXSALEINVOICEHEADER SHD " +
" INNER JOIN ax.ACXCUSTMASTER CUS ON SHD.CUSTOMER_CODE=CUS.CUSTOMER_CODE " +
" INNER JOIN ax.ACXUSERMASTER USM ON SHD.SITEID= USM.SITE_CODE " +
" INNER JOIN AX.INVENTSITE IST ON IST.SITEID = SHD.SITEID " +
"where INVOICE_NO in (" + multipleInvoice + ") and SHD.SITEID='" + Session["SiteCode"].ToString() + "' order by SHD.INVOICE_NO ";
string queryLine = @"Select ROW_NUMBER() over (ORDER BY SINVL.Amount Desc,PROD.PRODUCT_SUBCATEGORY, PRODUCT_NAME) AS SRNO,case when amount<>0 then 'A' else 'B' end as SortFilter
,PROD.PRODUCT_SUBCATEGORY ,SINVL.Invoice_No, SINVL.PRODUCT_CODE,
PRODUCT_NAME as PRODUCT_NAME,
CAST(PROD.PRODUCT_PACKSIZE as decimal(9,2)) as PRODUCT_PACKSIZE ,
CAST(SINVL.BOXPCS as decimal(9,2)) as BOX,CAST(SINVL.BOX as decimal(9,2)) as BoxConv,CAST(SINVL.MRP as decimal(9,2)) as MRP,
CAST((SINVL.BOX * SINVL.MRP) as decimal(9,2)) as MRPValue,
CAST(SINVL.LTR as decimal(9,2)) as LTR,
CAST(SINVL.TAX_CODE as decimal(9,2)) as TAX_CODE, CAST(SINVL.CRATES as decimal(9,2)) as CRATES,
CAST(SINVL.AMOUNT as decimal(9,2)) as AMOUNT, CAST(SINVL.TAX_AMOUNT as decimal(9,2)) as TAX_AMOUNT,
CAST(SINVL.LINEAMOUNT as decimal(9,2)) as LINEAMOUNT, CAST(SINVL.ADDTAX_AMOUNT as decimal(9,2)) as ADDTAX_AMOUNT,
CAST(SINVL.ADDTAX_CODE as decimal(9,2)) as ADDTAX_CODE, CAST(SINVL.DISC_AMOUNT as decimal(9,2)) as DISC_AMOUNT
,SINVL.SEC_DISC_AMOUNT,isnull(SINVL.PEVAlue,0) as PEValue,isnull(TDValue,0) as TDValue ,
CAST(SINVL.AMOUNT as Decimal(9,2)) -
CAST(SINVL.TAX_AMOUNT as Decimal(9,2)) -
CAST(SINVL.ADDTAX_AMOUNT as decimal(9,2)) + isnull(TDValue,0) -
Isnull(SINVL.PEVAlue,0) + CAST(SINVL.DISC_AMOUNT as decimal(9,2)) + SINVL.SEC_DISC_AMOUNT as basic,
Case when SINVL.BOX = 0 then 0.00 else (
Cast((CAST(SINVL.AMOUNT as Decimal(9,2)) -
CAST(SINVL.TAX_AMOUNT as Decimal(9,2)) -
CAST(SINVL.ADDTAX_AMOUNT as decimal(9,2)) + isnull(TDValue,0) -
Isnull(SINVL.PEVAlue,0) + CAST(SINVL.DISC_AMOUNT as decimal(9,2)) + SINVL.SEC_DISC_AMOUNT) / CAST(SINVL.BOX as decimal(9,2)) as decimal(9,2))
) End as RATE,
CAST(SINVL.AMOUNT as Decimal(9,2)) - CAST(SINVL.TAX_AMOUNT as Decimal(9,2)) - CAST(SINVL.ADDTAX_AMOUNT as decimal(9,2)) as TaxableAmt
from ax.ACXSALEINVOICELINE SINVL
INNER JOIN ax.inventtable PROD ON SINVL.PRODUCT_CODE=PROD.ITEMID " +
" where INVOICE_NO in (" + multipleInvoice + ") and SINVL.SITEID='" + Session["SiteCode"].ToString() + "' Order By INVOICE_NO ,SortFilter,Product_SubCategory ";
DataTable dtMainHeader1 = new DataTable();
dtMainHeader1 = obj.GetData(queryHeader);
DataTable dtLine1 = new DataTable();
dtLine1 = obj.GetData(queryLine);
string[] strSavePDFID = new string[dtMainHeader1.Rows.Count + 1];
//for merging a pdf into single one,I want extra pdf and for this i have called SaveInvoicePDf funtion
//for single invoice PDF i have created 2 pdf and murge into one
SaveInvoicePDf(dtMainHeader1, dtLine1, 0, 0, strSavePDFID);
//
for (int i = 0; i < dtMainHeader1.Rows.Count; i++)
{
SaveInvoicePDf(dtMainHeader1, dtLine1, i, i + 1, strSavePDFID);
}
if (strSavePDFID.Length > 0)
{
string des = Server.MapPath("Downloads\\" + strSavePDFID[0] + ".pdf");
string[] source = new string[strSavePDFID.Length - 1];
for (int i = 0; i < strSavePDFID.Length - 1; i++)
{
source[i] = Server.MapPath("Downloads\\" + strSavePDFID[i + 1] + ".pdf");
}
MergeFiles(des, source);
string FilePath = Server.MapPath("Downloads\\" + strSavePDFID[0] + ".pdf");
WebClient User = new WebClient();
Byte[] FileBuffer = User.DownloadData(FilePath);
//Delete the files from system..........
if (strSavePDFID.Length > 0)
{
for (int i = 0; i < strSavePDFID.Length; i++)
{
string path = Server.MapPath("Downloads\\" + strSavePDFID[i] + ".pdf");
FileInfo file = new FileInfo(path);
if (file.Exists)//check file exsit or not
{
file.Delete();
}
}
}
if (FileBuffer != null)
{
Response.AddHeader("Content-Disposition", "attachment;filename=" + strSavePDFID[0] + ".pdf");
Response.BinaryWrite(FileBuffer);
Response.Flush();
Response.End();
}
}
// murgebytges();
}
protected void ShowData_ForExcel()
{
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;
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 = "[dbo].[ACX_GETUSERDETAILS]";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@State", ddlState.SelectedItem.Value);
if (ddlSiteId.SelectedItem.Value == "All")
{
cmd.Parameters.AddWithValue("@SITECODE", "");
}
else
{
cmd.Parameters.AddWithValue("@SITECODE", ddlSiteId.SelectedItem.Value);
}
if (rdoDistributor.Checked == true)
{
cmd.Parameters.AddWithValue("@USERTYPE", "DISTRIBUTOR");
}
else
{
cmd.Parameters.AddWithValue("@USERTYPE", "PSRVRS");
}
dtDataByfilter = new DataTable();
dtDataByfilter.Load(cmd.ExecuteReader());
DataTable dt = new DataTable();
dt = dtDataByfilter;
//=================Create Excel==========
string attachment = "attachment; filename=UserLoginDetails.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)
{
StringWriter strwritter = new StringWriter();
HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);
tab = "";
for (i = 0; i < dt.Columns.Count; i++)
{
//string style = @" .text { mso-number-format:\@; } ";
//Response.Write(style);
//Response.ContentType = "application/text";
Response.Write(tab + dr[i].ToString());
Response.Write(dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.End();
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
}
}
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);
}
}
protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
{
string statesel = "";
foreach (System.Web.UI.WebControls.ListItem litem1 in lstState.Items)
{
if (litem1.Selected)
{
if (statesel.Length == 0)
{
statesel = "'" + litem1.Value.ToString() + "'";
}
else
{
statesel += ",'" + litem1.Value.ToString() + "'";
}
}
}
if (lstState.SelectedValue == string.Empty)
{
lstSiteId.Items.Clear();
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);
DataTable distinctValues = view.ToTable(true, "Distributor", "DistributorName");
lstSiteId.DataSource = distinctValues;
string AllSitesFromHierarchy = "";
foreach (DataRow row in distinctValues.Rows)
{
if (AllSitesFromHierarchy == "")
{
AllSitesFromHierarchy += "'" + row["Distributor"].ToString() + "'";
}
else
{
AllSitesFromHierarchy += ",'" + row["Distributor"].ToString() + "'";
}
}
if (AllSitesFromHierarchy != "")
{
string sqlstr1 = @"Select Distinct SITEID as Code,SITEID +' - '+ Name AS Name,Name as SiteName from [ax].[INVENTSITE] IV LEFT JOIN AX.ACXUSERMASTER UM on IV.SITEID = UM.SITE_CODE where SITEID IN (" + AllSitesFromHierarchy + ") Order by SiteName ";
dt = baseObj.GetData(sqlstr1);
lstSiteId.DataSource = dt;
lstSiteId.DataTextField = "Name";
lstSiteId.DataValueField = "Code";
lstSiteId.DataBind();
}
}
else
{
string sqlstr = "select * from ax.ACXSITEMENU where SITE_CODE ='" + Session["SiteCode"].ToString() + "'";
object objcheckSitecode = baseObj.GetScalarValue(sqlstr);
if (objcheckSitecode != null)
{
lstSiteId.Items.Clear();
string sqlstr1 = @"Select Distinct SITEID as Code,SITEID + ' - ' + NAME AS NAME,Name as SiteName from [ax].[INVENTSITE] IV JOIN AX.ACXUSERMASTER UM on IV.SITEID=UM.SITE_CODE where STATECODE in (" + statesel + ") Order by SiteName";
DataTable dt = baseObj.GetData(sqlstr1);
lstSiteId.DataSource = dt;
lstSiteId.DataTextField = "Name";
lstSiteId.DataValueField = "Code";
lstSiteId.DataBind();
}
else
{
lstSiteId.Items.Clear();
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
DataTable dt = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE);
dt.DefaultView.RowFilter = "STATE in (" + statesel + ")";
DataTable uniqueCols = dt.DefaultView.ToTable(true, "Distributor", "DistributorName");
string AllSitesFromHierarchy = "";
foreach (DataRow row in uniqueCols.Rows)
{
if (AllSitesFromHierarchy == "")
{
AllSitesFromHierarchy += "'" + row["Distributor"].ToString() + "'";
}
else
{
AllSitesFromHierarchy += ",'" + row["Distributor"].ToString() + "'";
}
}
string sqlstr1 = @"Select Distinct SITEID as Code,SITEID +' - '+ Name AS Name,Name as SiteName from [ax].[INVENTSITE] IV LEFT JOIN AX.ACXUSERMASTER UM on IV.SITEID = UM.SITE_CODE where SITEID IN (" + AllSitesFromHierarchy + ") Order by SiteName ";
dt = baseObj.GetData(sqlstr1);
lstSiteId.DataSource = dt;
lstSiteId.DataTextField = "Name";
lstSiteId.DataValueField = "Code";
lstSiteId.DataBind();
}
else
{
string sqlstr1 = @"Select Distinct SITEID as Code,SITEID + ' - ' + NAME AS NAME from [ax].[INVENTSITE] IV JOIN AX.ACXUSERMASTER UM on IV.SITEID=UM.SITE_CODE where SITEID = '" + Session["SiteCode"].ToString() + "'";
DataTable dt = baseObj.GetData(sqlstr1);
lstSiteId.DataSource = dt;
lstSiteId.DataTextField = "Name";
lstSiteId.DataValueField = "Code";
lstSiteId.DataBind();
}
}
}
if (lstSiteId.Items.Count == 1)
{
foreach (System.Web.UI.WebControls.ListItem litem in lstSiteId.Items)
{
litem.Selected = true;
}
}
Session["SalesData"] = App_Code.Global.HierarchyDataTable(ref chkListHOS, ref chkListVP, ref chkListGM, ref chkListDGM, ref chkListRM, ref chkListZM, ref chkListASM, ref chkListEXECUTIVE);
}
