private DataTable GetData(DateTime fromdate, DateTime todate, string targettype, string targetcategory, string targetsubcategory, string objecttype, string objectcode)
{
DataTable dt = null;
try
{
SqlConnection con = baseObj.GetConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = string.Empty;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ACX_Target_PURCHASE";
cmd.Parameters.Add("@SiteId", SqlDbType.NVarChar).Value = Convert.ToString(Session["SiteCode"]);
cmd.Parameters.Add("@FromDate", SqlDbType.SmallDateTime).Value = fromdate.ToString("yyyy-MM-dd");
cmd.Parameters.Add("@ToDate", SqlDbType.SmallDateTime).Value = todate.ToString("yyyy-MM-dd");
cmd.Parameters.Add("@TargetType", SqlDbType.Int).Value = targettype; //Convert.ToString(Session["SiteCode"]);
cmd.Parameters.Add("@TargetCategory", SqlDbType.NVarChar).Value = targetcategory; // Convert.ToString(Session["SiteCode"]);
cmd.Parameters.Add("@TargetSubCategory", SqlDbType.NVarChar).Value = targetsubcategory; // Convert.ToString(Session["SiteCode"]);
cmd.Parameters.Add("@ObjectType", SqlDbType.NVarChar).Value = objecttype; // Convert.ToString(Session["SiteCode"]);
cmd.Parameters.Add("@ObjectCode", SqlDbType.NVarChar).Value = objectcode; // Convert.ToString(Session["SiteCode"]);
cmd.Parameters.Add("@BUCode", SqlDbType.NVarChar).Value = ddlBusinessUnit.SelectedItem.Value; // Convert.ToString(Session["SiteCode"]);
SqlDataAdapter ad = new SqlDataAdapter(cmd);
dt = new DataTable();
ad.Fill(dt);
return(dt);
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
return(dt);
}
}
//on Button Save insert data in Table
protected void btnSave_Click(object sender, EventArgs e)
{
if (drpInvNo.SelectedItem.Value == "Select...")
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "Error", "alert('Please Select Invoice Number');", true); return;
}
else
{
try
{
DataTable reversal = ViewState["UpdatedReversalLine"] as DataTable;
if (reversal == null)
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "Error", "alert('No Line Amount is changed ');", true); return;
}
else
{
reversal.TableName = "ServiceLineItems";
string result = string.Empty;
using (StringWriter sw = new StringWriter())
{
reversal.WriteXml(sw);
result = sw.ToString();
}
conn = obj.GetConnection();
cmd = new SqlCommand("USP_INS_REVSERVICEBILL");
cmd.Connection = conn;
cmd.CommandTimeout = 3600;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
cmd.Parameters.AddWithValue("@SITEID", Session["SiteCode"].ToString());
cmd.Parameters.AddWithValue("@REFRENCEDOCNO", drpInvNo.SelectedItem.Value);
cmd.Parameters.AddWithValue("@REMARK", txtRemarks.Text);
cmd.Parameters.AddWithValue("@USERCODE", Session["USERID"].ToString());
cmd.Parameters.AddWithValue("@XmlData", result);
cmd.Parameters.AddWithValue("@DOCUMENTNO", "");
DataTable _dt = new DataTable();
_dt.Load(cmd.ExecuteReader());
Object _documentNum = _dt.Rows[0][0];
string _documentNumber = _documentNum.ToString();
conn.Dispose();
ScriptManager.RegisterStartupScript(this, typeof(Page), "Error", "alert('" + "Document Number:" + " " + _documentNumber + "');", true);
drpInvNo.Items.Clear();
BindInvoiceNo();
clearAll();
}
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
ScriptManager.RegisterStartupScript(this, typeof(Page), "Error", "alert('" + ex.Message.ToString().Replace("'", "''") + "');", true); return;
}
}
}
protected void lnkbtn_Click(object sender, EventArgs e)
{
try
{
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
conn = obj.GetConnection();
GridViewRow gvrow = (GridViewRow)(((LinkButton)sender)).NamingContainer;
LinkButton lnk = sender as LinkButton;
string Siteid = ((HiddenField)gvrow.FindControl("hndSiteid")).Value.ToString();
adp1 = new SqlDataAdapter("EXEC USP_SERVICEINVOICELINEDETAIL '" + lnk.Text + "','" + Siteid.ToString() + "'", conn);
ds1.Clear();
adp1.Fill(ds1, "dtl");
if (ds1.Tables["dtl"].Rows.Count != 0)
{
GridView2.DataSource = ds1;
GridView2.DataBind();
}
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
conn.Close();
conn.Dispose();
}
}
private void BindGrid()
{
string sitecode1;
try
{
sitecode1 = Session["SiteCode"].ToString();
DataTable dt = new DataTable();
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
conn = obj.GetConnection();
adp1 = new SqlDataAdapter("EXEC ACX_USP_INDENTVSINVOICE '" + txtFromDate.Text + "','" + txtToDate.Text + "','" + txtIndentNo.Text + "','" + Session["LOGINTYPE"].ToString() + "','" + Session["USERID"].ToString() + "'", conn);
adp1.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
}
}
public static List <string> GetProductDescription(string prefixText)
{
List <string> customers = new List <string>();
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
SqlConnection conn = obj.GetConnection();
try
{
using (SqlCommand cmd = new SqlCommand())
{
// CreamBell_DMS_WebApps.frmInvoicePrepration f1 = new CreamBell_DMS_WebApps.frmInvoicePrepration();
if (ddl1.SelectedItem.Text == "SO No")
{
cmd.CommandText = "select Top 20 SO_No as Code from [ax].[ACXSALESHEADER] where " +
"replace(replace(SO_No, char(9), ''), char(13) + char(10), '') Like @Code+'%' and SiteId = '" + siteid + "'";
}
if (ddl1.SelectedItem.Text == "Load Sheet No")
{
cmd.CommandText = "Select distinct Top 20 LOADSHEET_NO as Code from ax.ACXSALEINVOICEHEADER where " +
" replace(replace(LOADSHEET_NO, char(9), ''), char(13) + char(10), '') Like @Code+'%' and SiteId = '" + siteid + "'";
}
if (ddl1.SelectedItem.Text == "Customer Name")
{
cmd.CommandText = "select Top 20 CUSTOMER_CODE +'_'+ CUSTOMER_NAME as Code from ax.Acxcustmaster where " +
"replace(replace(CUSTOMER_NAME, char(9), ''), char(13) + char(10), '') Like @Code +'%' " +
" or replace(replace(CUSTOMER_CODE, char(9), ''), char(13) + char(10), '') Like @Code+'%' and Site_Code ='" + siteid + "'";
}
cmd.Parameters.AddWithValue("@Code", prefixText);
cmd.Connection = conn;
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(sdr["Code"].ToString());
}
}
conn.Close();
return(customers);
}
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
string str = ex.Message; return(customers);
}
}
protected void lnkbtnstatus_Click(object sender, EventArgs e)
{
string query = string.Empty;
conn = baseObj.GetConnection();
cmd = new SqlCommand();
cmd.Connection = conn;
LinkButton btn = sender as LinkButton;
GridViewRow row = (GridViewRow)(((LinkButton)sender)).NamingContainer;
string recid = ((HiddenField)row.FindControl("hndrecid")).Value.ToString();
try
{
if (btn.Text == "ACTIVE")
{
query = "update AX.ACXNOTIFICATIONMASTER set ISACTIVE=0 WHERE RECID='" + recid + "'";
cmd.CommandText = query;
cmd.ExecuteNonQuery();
ShowNotifications();
}
else
{
query = "update AX.ACXNOTIFICATIONMASTER set ISACTIVE=1 WHERE RECID='" + recid + "'";
cmd.CommandText = query;
cmd.ExecuteNonQuery();
ShowNotifications();
}
}
catch (Exception ex)
{
lblMessage.Visible = true;
lblMessage.Text = "► " + ex.Message.ToString();
}
finally
{
if (conn != null)
{
if (conn.State.ToString() == "Open")
{
conn.Close();
}
}
}
}
public static List <string> GetProductDescription(string prefixText)
{
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
string query = "Select ITEMID +'-(' + PRODUCT_NAME+')' as PRODUCT_NAME, ITEMID,PRODUCT_GROUP, PRODUCT_SUBCATEGORY from ax.INVENTTABLE where " +
"replace(replace(ITEMID, char(9), ''), char(13) + char(10), '') Like @ProductCode+'%'";
SqlConnection conn = obj.GetConnection();
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@ProductCode", prefixText);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
List <string> ProductDetails = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
ProductDetails.Add(dt.Rows[i]["ITEMID"].ToString());
}
return(ProductDetails);
}
private void GridDetail()
{
string sitecode1;
try
{
sitecode1 = Session["SiteCode"].ToString();
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
conn = obj.GetConnection();
string str = "Select A.PURCH_RETURNNO as INVOICE_NO , A.PURCH_RETURNDATE as INVOIC_DATE, A.Purch_recieptno as SO_NO,case when CONVERT(VARCHAR(10),PURCH_RECIEPTDATE , 103)= '01/01/1900' then '' else CONVERT(VARCHAR(10),PURCH_RECIEPTDATE , 103) end AS SO_DATE, ('[' +A.SITE_CODE +']' + ' ' + B.NAME) as CUSTOMER , A.Return_docvalue as INVOICE_VALUE" +
" from ax.[ACXPURCHRETURNHEADER] A INNER JOIN ax.inventsite B ON A.SITE_CODE=B.SITEID" +
" where A.SITE_CODE= '" + sitecode1 + "' and PURCH_RETURNDATE>=DateAdd(Day,-1,getdate()) and PURCH_RETURNDATE<=getdate() order by A.PURCH_RETURNDATE desc,A.PURCH_RETURNNO desc";
adp1 = new SqlDataAdapter("Select A.PURCH_RETURNNO as INVOICE_NO , A.PURCH_RETURNDATE as INVOIC_DATE, A.Purch_recieptno as SO_NO,case when CONVERT(VARCHAR(10),PURCH_RECIEPTDATE , 103)= '01/01/1900' then '' else CONVERT(VARCHAR(10),PURCH_RECIEPTDATE , 103) end AS SO_DATE, ('[' +A.SITE_CODE +']' + ' ' + B.NAME) as CUSTOMER , A.Return_docvalue as INVOICE_VALUE" +
" from ax.[ACXPURCHRETURNHEADER] A INNER JOIN ax.inventsite B ON A.SITE_CODE=B.SITEID" +
" where A.SITE_CODE= '" + sitecode1 + "' and PURCH_RETURNDATE>=DateAdd(Day,-1,getdate()) and PURCH_RETURNDATE<=getdate() order by A.PURCH_RETURNDATE desc,A.PURCH_RETURNNO desc", conn);
ds2.Clear();
adp1.Fill(ds2, "dtl");
if (ds2.Tables["dtl"].Rows.Count != 0)
{
for (int i = 0; i < ds2.Tables["dtl"].Rows.Count; i++)
{
GridView1.DataSource = ds2.Tables["dtl"];
GridView1.DataBind();
}
}
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
}
}
protected void lnkbtn_Click(object sender, EventArgs e)
{
try
{
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
conn = obj.GetConnection();
GridViewRow gvrow = (GridViewRow)(((LinkButton)sender)).NamingContainer;
LinkButton lnk = sender as LinkButton;
//adp1 = new SqlDataAdapter("select a.CUSTOMER_CODE,a.INVOICE_NO,a.LINE_NO,a.PRODUCT_CODE,a.AMOUNT,a.BOX,a.CRATES,a.LTR,a.QUANTITY," +
// " a.MRP,a.RATE,a.UOM,a.TAX_CODE,a.TAX_AMOUNT,a.DISC_AMOUNT,a.SEC_DISC_AMOUNT,b.product_group,b.product_name" +
// " from ax.ACXSALEINVOICELINE a, ax.ACXProductMaster b " +
// " where a.INVOICE_NO = '" + lnk.Text + "' and a.product_code = b.product_code ", conn);
adp1 = new SqlDataAdapter("select a.CUSTOMER_CODE,a.INVOICE_NO,a.LINE_NO,a.PRODUCT_CODE,a.AMOUNT,a.BOX,a.CRATES,a.LTR,a.QUANTITY," +
" a.MRP,a.RATE,a.UOM,a.TAX_CODE,a.TAX_AMOUNT,a.DISC_AMOUNT,a.SEC_DISC_AMOUNT,b.product_group,b.product_name,BOXQTY,PCSQty,BOXPCS " +
" from ax.ACXSALEINVOICELINE a, ax.InventTable b " +
" where a.INVOICE_NO = '" + lnk.Text + "' and a.product_code = b.ItemId and a.SiteID='" + Session["SiteCode"].ToString() + "' ", conn);
ds1.Clear();
adp1.Fill(ds1, "dtl");
if (ds1.Tables["dtl"].Rows.Count != 0)
{
GridView2.DataSource = ds1;
GridView2.DataBind();
}
}
catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); }
finally
{
conn.Close();
conn.Dispose();
}
}
private void GridDetail()
{
string sitecode1;
try
{
sitecode1 = Session["SiteCode"].ToString();
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
conn = obj.GetConnection();
string FromDate = Convert.ToDateTime(txtFromDate.Text).ToString("dd-MMM-yyyy");
string ToDate = Convert.ToDateTime(txtToDate.Text).ToString("dd-MMM-yyyy");
string query = "EXEC USP_SERVICEINVOICEDETAIL '" + sitecode1 + "','" + FromDate + "','" + ToDate + "'";
adp1 = new SqlDataAdapter(query, conn);
ds2.Clear();
adp1.Fill(ds2, "dtl");
if (ds2.Tables["dtl"].Rows.Count != 0)
{
for (int i = 0; i < ds2.Tables["dtl"].Rows.Count; i++)
{
GridView1.DataSource = ds2.Tables["dtl"];
GridView1.DataBind();
}
}
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
}
}
private 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 IN (" + ucRoleFilters.GetCommaSepartedSiteId() + ") ";
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 = "ACX_PartyWiseSaleSummaryDiscount_ExcelOutput";
cmd.CommandText = query;
DateTime now = Convert.ToDateTime(txtToDate.Text);
now = now.AddMonths(1);
cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd"));
string SiteList = "";
string StateList = "";
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
SiteList = ucRoleFilters.GetCommaSepartedSiteId();
StateList = ucRoleFilters.GetCommaSepartedStateId();
}
else
{
foreach (ListItem item in chkListSite1.Items)
{
if (item.Selected)
{
if (SiteList == "")
{
SiteList += "" + item.Value.ToString() + "";
}
else
{
SiteList += "," + item.Value.ToString() + "";
}
}
}
foreach (ListItem item in chkListState1.Items)
{
if (item.Selected)
{
if (StateList == "")
{
StateList += "" + item.Value.ToString() + "";
}
else
{
StateList += "," + item.Value.ToString() + "";
}
}
}
}
if (SiteList.Length > 0)
{
cmd.Parameters.AddWithValue("@SiteId", SiteList);
}
else
{
cmd.Parameters.AddWithValue("@SiteId", "");
}
if (StateList.Length > 0)
{
cmd.Parameters.AddWithValue("@STATECODE", StateList);
}
else
{
cmd.Parameters.AddWithValue("@STATECODE", "");
}
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", "");
}
dtDataByfilter = new DataTable();
cmd.Connection = obj.GetConnection();
dtDataByfilter.Load(cmd.ExecuteReader());
DataTable dt = new DataTable();
#region product group
string ProductGroupList = "";
foreach (ListItem item in chkProductGroup.Items)
{
if (item.Selected)
{
if (ProductGroupList == "")
{
ProductGroupList += "'" + item.Value.ToString() + "'";
}
else
{
ProductGroupList += ",'" + item.Value.ToString() + "'";
}
}
}
if (ProductGroupList.Length > 0)
{
dt = dtDataByfilter.Select("PRODUCT_GROUP IN (" + ProductGroupList + ")").CopyToDataTable();
}
else
{
dt = dtDataByfilter;
}
#endregion
//dt = dtDataByfilter;
//=================Create Excel==========
string attachment = "attachment; filename=PartyWiseSaleSummaryDiscount.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)
{
LblMessage.Text = ex.Message.ToString();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally {
obj.CloseSqlConnection();
}
}
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 query1 = "Select NAME from ax.inventsite where SITEID IN (" + ucRoleFilters.GetCommaSepartedSiteId() + ") ";
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;
// string qr = "";
query = "[ax].[ACX_PartyWiseSaleSummaryDiscount]";
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).ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd"));
string SiteList = "";
string StateList = "";
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
SiteList = ucRoleFilters.GetCommaSepartedSiteId();
StateList = ucRoleFilters.GetCommaSepartedStateId();
}
else
{
foreach (ListItem item in chkListSite1.Items)
{
if (item.Selected)
{
if (SiteList == "")
{
SiteList += "" + item.Value.ToString() + "";
}
else
{
SiteList += "," + item.Value.ToString() + "";
}
}
}
foreach (ListItem item in chkListState1.Items)
{
if (item.Selected)
{
if (StateList == "")
{
StateList += "" + item.Value.ToString() + "";
}
else
{
StateList += "," + item.Value.ToString() + "";
}
}
}
}
if (SiteList.Length > 0)
{
cmd.Parameters.AddWithValue("@SiteId", SiteList);
}
else
{
cmd.Parameters.AddWithValue("@SiteId", "");
}
if (StateList.Length > 0)
{
cmd.Parameters.AddWithValue("@STATECODE", StateList);
}
else
{
cmd.Parameters.AddWithValue("@STATECODE", "");
}
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", "");
}
cmd.Connection = obj.GetConnection();
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 IN (" + ucRoleFilters.GetCommaSepartedSiteId() + ") and INVOICE_DATE >=" +
" '" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "' and INVOICE_DATE <='" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "' group by SP.CUSTOMER_NAME";
dtSetData = new DataTable();
#region product group
string ProductGroupList = "";
foreach (ListItem item in chkProductGroup.Items)
{
if (item.Selected)
{
if (ProductGroupList == "")
{
ProductGroupList += "'" + item.Value.ToString() + "'";
}
else
{
ProductGroupList += ",'" + item.Value.ToString() + "'";
}
}
}
if (ProductGroupList.Length > 0)
{
dtSetData = dtDataByfilter.Select("PRODUCT_GROUP IN (" + ProductGroupList + ")").CopyToDataTable();
}
else
{
dtSetData = dtDataByfilter;
}
#endregion
dtTotalInvoiceNo = obj.GetData(queryTotInv);
LoadDataInReportViewer(dtSetHeader, dtSetData, dtTotalInvoiceNo);
}
catch (Exception ex)
{
LblMessage.Text = ex.Message.ToString();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
obj.CloseSqlConnection();
}
}
protected void btn2_Click(object sender, EventArgs e)
{
string sitecode1;
sitecode1 = Session["SiteCode"].ToString();
gvDetails.DataSource = null;
gvDetails.DataBind();
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
conn = obj.GetConnection();
try
{
if (drpSearch.SelectedItem.Text == "SO Number")
{
adp1 = new SqlDataAdapter("Select h.SO_No,h.[DELIVERY_DATE],C.CUST_GROUP,h.Customer_Code" +
",C.CUSTOMER_NAME,C.ADDRESS1,APB.BeatName ,C.PSR_BEAT,h.SO_DATE,SO_Value,psr.PSR_Name , h.PSR_CODE,h.PSR_CODE,case when h.App_SO_NO!='' then 'MOBILE' else 'MANUAL' end as SOTYPE " +
" from [ax].[ACXSALESHEADER] h Inner Join " +
" [ax].[ACXCUSTMASTER] C on h.Customer_Code = C.Customer_Code" +
//" and C.[DATAAREAID] = h.[DATAAREAID] " +
" left join [ax].[ACXPSRBeatMaster] APB on c.PSR_BEAT = APB.BeatCode and h.PSR_CODE = APB.PSRCode " +
" left join [ax].[ACXPSRMaster] psr on h.PSR_CODE = psr.PSR_Code " +
" where loadsheet_Status = 0 and h.siteid = '" + sitecode1 + "' " +
" and h.SO_NO like '%" + txtSerch.Text + "%' " +
" order by h.[DELIVERY_DATE] asc,h.SO_DATE asc", conn);
}
else if (drpSearch.SelectedItem.Text == "Date")
{
adp1 = new SqlDataAdapter("Select h.SO_No,h.[DELIVERY_DATE],C.CUST_GROUP,h.Customer_Code" +
",C.CUSTOMER_NAME,C.ADDRESS1,APB.BeatName ,C.PSR_BEAT,h.SO_DATE,SO_Value,psr.PSR_Name ,h.PSR_CODE,case when h.App_SO_NO!='' then 'MOBILE' else 'MANUAL' end as SOTYPE " +
" from [ax].[ACXSALESHEADER] h Inner Join " +
" [ax].[ACXCUSTMASTER] C on h.Customer_Code = C.Customer_Code" +
//" and C.[DATAAREAID] = h.[DATAAREAID] " +
" left join [ax].[ACXPSRBeatMaster] APB on c.PSR_BEAT = APB.BeatCode and h.PSR_CODE = APB.PSRCode " +
" left join [ax].[ACXPSRMaster] psr on h.PSR_CODE = psr.PSR_Code " +
" where loadsheet_Status = 0 and h.siteid = '" + sitecode1 + "' " +
" and h.SO_DATE = '" + Convert.ToDateTime(txtSerch.Text).ToString("dd-MMM-yyyy") + "' " +
" order by h.[DELIVERY_DATE] asc,h.SO_DATE asc", conn);
}
else if (drpSearch.SelectedItem.Text == "Customer Group")
{
adp1 = new SqlDataAdapter("Select h.SO_No,h.[DELIVERY_DATE],C.CUST_GROUP,h.Customer_Code" +
",C.CUSTOMER_NAME,C.ADDRESS1,APB.BeatName ,C.PSR_BEAT,h.SO_DATE,SO_Value,psr.PSR_Name ,h.PSR_CODE,case when h.App_SO_NO!='' then 'MOBILE' else 'MANUAL' end as SOTYPE " +
" from [ax].[ACXSALESHEADER] h Inner Join " +
" [ax].[ACXCUSTMASTER] C on h.Customer_Code = C.Customer_Code" +
// " and C.[DATAAREAID] = h.[DATAAREAID] " +
" left join [ax].[ACXPSRBeatMaster] APB on c.PSR_BEAT = APB.BeatCode and h.PSR_CODE = APB.PSRCode " +
" left join [ax].[ACXPSRMaster] psr on h.PSR_CODE = psr.PSR_Code " +
" where loadsheet_Status = 0 and h.siteid = '" + sitecode1 + "' " +
" and c.CUST_GROUP like '%" + txtSerch.Text + "%' " +
" order by h.[DELIVERY_DATE] asc,h.SO_DATE asc", conn);
}
else
{
adp1 = new SqlDataAdapter("Select h.SO_No,h.[DELIVERY_DATE],C.CUST_GROUP,h.Customer_Code" +
",C.CUSTOMER_NAME,C.ADDRESS1,APB.BeatName ,C.PSR_BEAT,h.SO_DATE,SO_Value,psr.PSR_Name,h.PSR_CODE,case when h.App_SO_NO!='' then 'MOBILE' else 'MANUAL' end as SOTYPE " +
" from [ax].[ACXSALESHEADER] h Inner Join " +
" [ax].[ACXCUSTMASTER] C on h.Customer_Code = C.Customer_Code" +
//" and C.[DATAAREAID] = h.[DATAAREAID] " +
" left join [ax].[ACXPSRBeatMaster] APB on c.PSR_BEAT = APB.BeatCode and h.PSR_CODE = APB.PSRCode " +
" left join [ax].[ACXPSRMaster] psr on h.PSR_CODE = psr.PSR_Code " +
" where loadsheet_Status = 0 and h.siteid = '" + sitecode1 + "'" +
" and h.PSR_CODE like '%" + txtSerch.Text + "%' " +
" order by h.[DELIVERY_DATE] asc,h.SO_DATE asc", conn);
}
ds2.Clear();
adp1.Fill(ds2, "dtl");
if (ds2.Tables["dtl"].Rows.Count != 0)
{
gvDetails.DataSource = ds2;
gvDetails.DataBind();
}
GridView2.DataSource = null;
GridView2.DataBind();
GridView2.Visible = false;
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
}
}
public void SaveDetails()
{
try
{
dt = new DataTable();
//============Generate Doc Number=============
#region getnTUMBER
string DocumentNo = string.Empty;
string query = "SELECT ISNULL(MAX(CAST(RIGHT([Document_No],6) AS INT)),0)+1 FROM [ax].[ACXPAYMENTENTRY] where SITECODE='" + Session["SiteCode"].ToString() + "'";
conn = obj.GetConnection();
cmd1 = new SqlCommand(query);
transaction = conn.BeginTransaction();
cmd1.Connection = conn;
cmd1.Transaction = transaction;
cmd1.CommandTimeout = 3600;
cmd1.CommandType = CommandType.Text;
object vc = cmd1.ExecuteScalar();
int NUMSEQ = (int)vc;
DocumentNo = "PY" + "-" + ((int)vc).ToString("000000");
#endregion
conn = baseObj.GetConnection();
cmd = new SqlCommand("ACX_PaymentEntry");
transaction = conn.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = transaction;
cmd.CommandTimeout = 3600;
cmd.CommandType = CommandType.StoredProcedure;
cmd1 = new SqlCommand("ACX_PaymentEntry");
cmd1.Connection = conn;
cmd1.Transaction = transaction;
cmd1.CommandTimeout = 3600;
cmd1.CommandType = CommandType.StoredProcedure;
//======Save PaymentEntry===================
decimal pendingAmount = 0;
string ReferenceDocDate, ReferenceDocNo;
int i = 0;
int count = 0;
foreach (GridViewRow gv in gvDetails.Rows)
{
i = i + 1;
HiddenField Suppliercode = (HiddenField)gv.Cells[0].FindControl("HiddenField2");
DropDownList InstrumentCode = (DropDownList)gv.Cells[2].FindControl("drpInstrument");
TextBox InstrumentNo = (TextBox)gv.Cells[3].FindControl("txtInstrument");
//DropDownList RefDocument_No = (DropDownList)gv.Cells[5].FindControl("drpRefDocument");
//Label RefDocument_Date = (Label)gv.Cells[6].FindControl("lblRefDocDate");
//Label PendingAmount = (Label)gv.Cells[7].FindControl("lblPendingAmount");
TextBox Amount = (TextBox)gv.Cells[8].FindControl("txtAmount");
TextBox Remark = gv.FindControl("txtRemark") as TextBox;
string PendingAmount = (gv.Cells[9].Text);
ReferenceDocDate = (gv.Cells[7].Text);
ReferenceDocNo = (gv.Cells[6].Text);
decimal amount;
if (Amount.Text == string.Empty)
{
amount = 0;
}
else
{
amount = Convert.ToDecimal(Amount.Text);
}
if (PendingAmount == string.Empty)
{
pendingAmount = 0;
}
else
{
pendingAmount = Convert.ToDecimal(PendingAmount);
}
if (amount > 0 && InstrumentCode.SelectedItem.Text != "--Select--")
{
string sitecode = Session["SiteCode"].ToString();
string DATAAREAID = Session["DATAAREAID"].ToString();
string Plant_Code = Suppliercode.Value.ToString();
count = count + 1;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@SiteCode", Session["SiteCode"].ToString());
cmd.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
cmd.Parameters.AddWithValue("@Plant_Code", Suppliercode.Value);
cmd.Parameters.AddWithValue("@Document_No", DocumentNo);
cmd.Parameters.AddWithValue("@Instrument_Code", InstrumentCode.SelectedItem.Value);
cmd.Parameters.AddWithValue("@Instrument_No", InstrumentNo.Text);
cmd.Parameters.AddWithValue("@Ref_DocumentNo", ReferenceDocNo.ToString());
cmd.Parameters.AddWithValue("@Ref_DocumentDate", ReferenceDocDate.ToString());
cmd.Parameters.AddWithValue("@Payment_Amount", amount);
cmd.Parameters.AddWithValue("@Payment_Date", txtPaymentDate.Text);
cmd.Parameters.AddWithValue("@Status", "INSERT");
cmd.Parameters.AddWithValue("@NUMSEQ", NUMSEQ);
cmd.Parameters.AddWithValue("@Remark", Remark.Text.Trim());
cmd.ExecuteNonQuery();
//===============Update Payment Ledger/Transaction Table===============
//cmd1.Parameters.Clear();
//string sitecode = Session["SiteCode"].ToString();
//double amt = Convert.ToDouble(pendingAmount-amount);
//cmd1.Parameters.AddWithValue("@SiteCode", Session["SiteCode"].ToString());
//cmd1.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
//cmd1.Parameters.AddWithValue("@Document_No", ReferenceDocNo.ToString());
//cmd1.Parameters.AddWithValue("@RemainingAmount", pendingAmount - amount);
//cmd1.Parameters.AddWithValue("@Status", "UPDATE");
//cmd1.Parameters.AddWithValue("@Remark", Remark.Text.Trim());
//
//cmd1.ExecuteNonQuery();
}
else
{
}
}
//============Commit All Data================
transaction.Commit();
if (count > 0)
{
this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Payment Entry Document No : " + DocumentNo + " Generated Successfully.!');", true);
}
BindGridview();
}
catch (Exception ex)
{
transaction.Rollback();
LblMessage.Text = "'Please Try Again!! !";
LblMessage.Visible = true;
uppanel.Update();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
conn.Close();
}
}
private void SaveData()
{
try
{
LblMessage.Text = "";
bool b = Validation();
if (b == true)
{
conn = obj.GetConnection();
string strCode = string.Empty;
cmd = new SqlCommand();
transaction = conn.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = transaction;
cmd.CommandTimeout = 3600;
DataTable dtNumSeq = obj.GetNumSequenceNew(3, Session["SiteCode"].ToString(), Session["DATAAREAID"].ToString()); //st.Substring(st.Length - 6) + System.DateTime.Now.ToString("yyMMddhhmmss");
string NUMSEQ = string.Empty;
if (dtNumSeq != null)
{
strCode = dtNumSeq.Rows[0][0].ToString();
NUMSEQ = dtNumSeq.Rows[0][1].ToString();
}
else
{
return;
}
cmd.CommandText = string.Empty;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ACX_ACXPURCHINVREVERSAL";
#region Header Insert Data
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@SITE_CODE", Session["SiteCode"].ToString());
cmd.Parameters.AddWithValue("@PURCH_RETURNNO", strCode);
cmd.Parameters.AddWithValue("@PURCH_RECIEPTNO", drpRecieptNo.Text.Trim().ToString());
cmd.Parameters.AddWithValue("@TRANSPORTER_CODE", txtDriverNumber.Text);
cmd.Parameters.AddWithValue("@VEHICAL_NO", txtVehicleNumber.Text);
cmd.Parameters.AddWithValue("@VEHICAL_TYPE", txtVehicleType.Text);
cmd.Parameters.AddWithValue("@RETURN_REASONCODE", drpReturnReason.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@REMARK", txtRemark.Text.Trim().ToString());
cmd.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
cmd.Parameters.AddWithValue("@NUMSEQ", NUMSEQ);
DataTable dtgrid = new DataTable();
if (Session[SessionGrid] != null)
{
dtgrid = (DataTable)Session[SessionGrid];
}
else
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "Validation", "alert('Session expired! Reselect the receipt no.');", true);
LblMessage.Text = "Session expired! Reselect the receipt no.";
return;
}
#endregion
DataSet ds = new DataSet();
DataTable dtLineItem = new DataTable();
dtLineItem = dtgrid.Copy();
dtLineItem.TableName = "PurchReturn";
ds.Tables.Add(dtLineItem);
string ReversalLineXml = ds.GetXml();
cmd.Parameters.AddWithValue("@XmlData", ReversalLineXml);
cmd.ExecuteNonQuery();
//using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
//{
// using (DataTable dtt = new DataTable())
// {
// sda.Fill(dtt);
// }
//}
//SaveManualPurchaseReturnToInventTransTable(strCode, transaction, conn);
transaction.Commit();
LblMessage.Text = "Purchase Return Order : " + strCode.ToString() + " Generated Successfully.!";
ScriptManager.RegisterStartupScript(this, typeof(Page), "Alert", " alert('Purchase Return Order : " + strCode.ToString() + " Generated Successfully.!');", true);
ClearAll();
//LblMessage.Text = "Inventory Affected Successfully.!";
//this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Inventory Affected Successfully.!');", true);
}
}
catch (Exception ex)
{
transaction.Rollback();
string message = "alert('Error:" + ex.Message.Replace("'", "") + " !');";
ScriptManager.RegisterStartupScript(this, typeof(Page), "Error", message, true);
LblMessage.Text = message;
ErrorSignal.FromCurrentContext().Raise(ex);
}
}
//protected void FillCustomerGroup()
//{
// DataTable dt = new DataTable();
// string sqlstr = "select Distinct CustGroup_Name as Name,Custgroup_Code as Code from ax.ACXCUSTGROUPMASTER ";
// dt = new DataTable();
// dt = baseObj.GetData(sqlstr);
// drpPSR.Items.Clear();
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// drpPSR.DataSource = dt;
// drpPSR.DataTextField = "NAME";
// drpPSR.DataValueField = "Code";
// drpPSR.DataBind();
// }
//}
protected void fillSiteAndState(DataTable dt)
{
try
{
string sqlstr = "";
if (Convert.ToString(Session["ISDISTRIBUTOR"]) == "Y")
{
if (Convert.ToString(Session["LOGINTYPE"]) == "3")
{
DataTable dtState = dt.DefaultView.ToTable(true, "STATE", "STATEWNAME");
// dtState.Columns.Add("STATENAMES", typeof(string), "STATE + ' - ' + STATENAME");
lstSTATE.Items.Clear();
DataRow dr = dtState.NewRow();
dr[0] = "--Select--";
dr[1] = "--Select--";
lstSTATE.DataSource = dtState;
lstSTATE.DataTextField = "STATEWNAME";
lstSTATE.DataValueField = "STATE";
lstSTATE.DataBind();
}
else
{
DataTable dt2 = new DataTable();
sqlstr = "Select Distinct I.StateCode Code,I.StateCode+'-'+LS.Name 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"]) + "'";
//lstSTATE.Items.Add("Select...");
SqlCommand cmd1 = new SqlCommand();
cmd1.Connection = baseObj.GetConnection();
cmd1.CommandText = sqlstr;
SqlDataAdapter sda = new SqlDataAdapter(cmd1);
sda.Fill(dt2);
//dt2.Load(cmd1.ExecuteReader());
lstSTATE.DataSource = dt2;
lstSTATE.DataTextField = "Name";
lstSTATE.DataValueField = "Code";
lstSTATE.DataBind();
}
}
else
{
DataTable dt1 = new DataTable();
sqlstr = "Select Distinct I.StateCode Code,I.StateCode+'-'+LS.Name Name from [ax].[INVENTSITE] I left join [ax].[LOGISTICSADDRESSSTATE] LS on LS.STATEID = I.STATECODE where I.STATECODE <>'' ";
lstSTATE.Items.Add("Select...");
//only name and code have to be insertd in a new datatable according to this sqlstr
SqlCommand cmd1 = new SqlCommand();
cmd1.Connection = baseObj.GetConnection();
cmd1.CommandText = sqlstr;
SqlDataAdapter sda = new SqlDataAdapter(cmd1);
sda.Fill(dt1);
lstSTATE.DataSource = dt1;
lstSTATE.DataTextField = "name";
lstSTATE.DataValueField = "code";
lstSTATE.DataBind();
}
if (lstSTATE.Items.Count == 1)
{
CheckBox7.Visible = false;
lstSTATE.Items[0].Selected = true;
//lstSTATE(null, null);
lstSTATE_SelectedIndexChanged(null, null);
}
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
}
}
private void Addloadsheetheader()
{
string productcode1;
int recid;
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
conn = obj.GetConnection();
adp1 = new SqlDataAdapter("SELECT Top (1) recid FROM ax.ACXLOADSHEETHEADER order by recid desc ", conn);
ds2.Clear();
adp1.Fill(ds2, "dtl");
if (ds2.Tables["dtl"].Rows.Count != 0)
{
productcode1 = string.Copy(ds2.Tables["dtl"].Rows[0][0].ToString());
recid = Convert.ToInt32((productcode1).ToString()) + 1;
}
else
{
recid = 1;
}
SqlTransaction transaction;
transaction = conn.BeginTransaction();
int k = 0;
try
{
SqlCommand comm = new SqlCommand();
SqlCommand comm1 = new SqlCommand();
SqlCommand comm2 = new SqlCommand();
SqlDataAdapter sqladp = new SqlDataAdapter();
DataTable dt = new DataTable();
string sono = "";
//string strLoadSheetNo = obj.GetNumSequence(5, Session["SiteCode"].ToString(), Session["DATAAREAID"].ToString());
DataTable dtNumSeq = obj.GetNumSequenceNew(5, Session["SiteCode"].ToString(), Session["DATAAREAID"].ToString()); //st.Substring(st.Length - 6) + System.DateTime.Now.ToString("yyMMddhhmmss");
string strLoadSheetNo = string.Empty;
string NUMSEQ = string.Empty;
if (dtNumSeq != null)
{
strLoadSheetNo = dtNumSeq.Rows[0][0].ToString();
NUMSEQ = dtNumSeq.Rows[0][1].ToString();
}
else
{
return;
}
bool flag = false;
string lodNO = string.Empty;
for (int i = 0; i < gvDetails.Rows.Count; i++)
{
if (((CheckBox)gvDetails.Rows[i].FindControl("chkStatus")).Checked)
{
sono = ((CheckBox)gvDetails.Rows[i].FindControl("chkStatus")).Text;
string sodate = gvDetails.Rows[i].Cells[1].Text;
string customergroup = gvDetails.Rows[i].Cells[2].Text;
string customercode = gvDetails.Rows[i].Cells[3].Text;
string psrname = gvDetails.Rows[i].Cells[6].Text;
string beatname = gvDetails.Rows[i].Cells[8].Text;
string deliverydate = gvDetails.Rows[i].Cells[10].Text;
string value = gvDetails.Rows[i].Cells[11].Text;
string sitecode1, DataAreaId;
sitecode1 = Session["SiteCode"].ToString();
DataAreaId = Session["DATAAREAID"].ToString();
decimal value1;
value1 = Convert.ToDecimal(value);
comm = new SqlCommand("ax.ACX_ACXLOADSHEETHEADER", conn, transaction);
comm.CommandType = CommandType.StoredProcedure;
comm.CommandTimeout = 0;
comm.Parameters.AddWithValue("@SITEID", sitecode1);
comm.Parameters.AddWithValue("@DATAAREAID", DataAreaId);
comm.Parameters.AddWithValue("@RECID", i + recid);
comm.Parameters.AddWithValue("@CUSTOMER_CODE", customercode);
comm.Parameters.AddWithValue("@LOADSHEET_NO", strLoadSheetNo);
lodNO = strLoadSheetNo;
comm.Parameters.AddWithValue("@SO_NO", sono);
comm.Parameters.AddWithValue("@SO_DATE", sodate);
comm.Parameters.AddWithValue("@PSR_CODE", psrname);
comm.Parameters.AddWithValue("@PSR_BEAT", beatname);
comm.Parameters.AddWithValue("@INVOICE_DATE", deliverydate);
comm.Parameters.AddWithValue("@DELIVERY_DATE", deliverydate);
comm.Parameters.AddWithValue("@VALUE", value1);
comm.Parameters.AddWithValue("@RATE", value1);
comm.Parameters.AddWithValue("@NUMSEQ", NUMSEQ);
int a1 = comm.ExecuteNonQuery();
string box = ""; decimal box2;
for (int j = 0; j < GridView2.Rows.Count; j++)
{
string productgroup = GridView2.Rows[j].Cells[0].Text;
string productcode = GridView2.Rows[j].Cells[1].Text;
string crate = GridView2.Rows[j].Cells[3].Text;
TextBox txtBox = ((TextBox)GridView2.Rows[j].FindControl("txtqty")); //with conv
TextBox txtPcs = ((TextBox)GridView2.Rows[j].FindControl("txtPCS")); //pcs
TextBox txtBoxQty = ((TextBox)GridView2.Rows[j].FindControl("txtBox")); //box qty
TextBox txtBoxPcs = ((TextBox)GridView2.Rows[j].FindControl("txtBoxPcs")); // boxpcs
box = txtBox.Text;
string Ltr = GridView2.Rows[j].Cells[8].Text;
string stockBox = GridView2.Rows[j].Cells[9].Text;
string StockLtr = GridView2.Rows[j].Cells[10].Text;
string sitecode2, DataAreaId2, uom;
uom = "";
sitecode2 = Session["SiteCode"].ToString();
DataAreaId2 = Session["DATAAREAID"].ToString();
decimal crate1, ltr1, stockBox1, StockLtr1;
stockBox1 = Convert.ToDecimal(stockBox);
StockLtr1 = Convert.ToDecimal(StockLtr);
crate1 = Convert.ToDecimal(crate);
box2 = Convert.ToDecimal(box);
ltr1 = Convert.ToDecimal(Ltr);
if (Convert.ToDecimal(txtBox.Text) <= Convert.ToDecimal(stockBox) && Convert.ToDecimal(txtBox.Text) > 0)
{
comm1 = new SqlCommand("ax.ACX_ACXLOADSHEETLINE", conn, transaction);
comm1.CommandType = CommandType.StoredProcedure;
comm1.CommandTimeout = 0;
comm1.Parameters.AddWithValue("@SHITEID", sitecode2);
comm1.Parameters.AddWithValue("@DATAAREAID", DataAreaId2);
comm1.Parameters.AddWithValue("@RECID", j + 1 + recid);
comm1.Parameters.AddWithValue("@CUSTOMER_CODE", customercode);
comm1.Parameters.AddWithValue("@LOADSHEET_NO", strLoadSheetNo);
comm1.Parameters.AddWithValue("@LINE_NO", j + 1);
comm1.Parameters.AddWithValue("@UOM", uom);
comm1.Parameters.AddWithValue("@STOCKQTY_BOX", stockBox1);
comm1.Parameters.AddWithValue("@STOCKQTY_LTR", StockLtr1);
comm1.Parameters.AddWithValue("@BOX", box2);
comm1.Parameters.AddWithValue("@CRATES", crate1);
comm1.Parameters.AddWithValue("@LINENUM", j + 1);
comm1.Parameters.AddWithValue("@LTR", ltr1);
comm1.Parameters.AddWithValue("@PRODUCT_CODE", productcode);
comm1.Parameters.AddWithValue("@BOXQty", Convert.ToDecimal(txtBoxQty.Text.Trim()));
comm1.Parameters.AddWithValue("@PcsQty", Convert.ToDecimal(txtPcs.Text.Trim()));
comm1.Parameters.AddWithValue("@BOXPcs", txtBoxPcs.Text.Trim());
int a = comm1.ExecuteNonQuery();
}
}
for (k = 0; k < gvDetails.Rows.Count; k++)
{
if (((CheckBox)gvDetails.Rows[k].FindControl("chkStatus")).Checked)
{
sono = ((CheckBox)gvDetails.Rows[k].FindControl("chkStatus")).Text;
sitecode1 = Session["SiteCode"].ToString();
DataAreaId = Session["DATAAREAID"].ToString();
comm2 = new SqlCommand("ax.ACX_ACXLOADSHEETHEADERUPDATE_LOADNO", conn, transaction);
comm2.CommandType = CommandType.StoredProcedure;
comm2.Parameters.AddWithValue("@LOADSHEET_NO", strLoadSheetNo);
comm2.Parameters.AddWithValue("@SO_NO", sono);
comm2.Parameters.AddWithValue("@SiteCode", sitecode1);
comm2.Parameters.AddWithValue("@DATAAREAID", DataAreaId);
int a2 = comm2.ExecuteNonQuery();
}
}
//obj.UpdateLastNumSequence(5, Session["SiteCode"].ToString(), Session["DATAAREAID"].ToString(), conn, transaction);
transaction.Commit();
flag = true;
break;
}
}
if (flag == true)
{
this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Load Sheet N0:" + lodNO + " generated Suucessfull!');", true);
Session["LSNO"] = lodNO;
}
}
catch (Exception ex)
{
transaction.Rollback();
conn.Close();
ErrorSignal.FromCurrentContext().Raise(ex);
}
}
protected void ReportShow()
{
string frmDate = Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd");
string endDate = Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd");
string psrcode = "";
var psrlist = drpPSR.Items.Cast <ListItem>().Where(it => it.Selected == true).Select(it => it.Value);
if (psrlist.Count() > 0)
{
psrcode = string.Join(",", psrlist);
}
//foreach (System.Web.UI.WebControls.ListItem litem in drpPSR.Items)
//{
// if (litem.Selected)
// {
// if (psrcode.Length == 0)
// psrcode = "" + litem.Value.ToString() + "";
// else
// psrcode += "," + litem.Value.ToString() + "";
// }
//}
string beatcode = "";
var beatcodelist = drpBEAT.Items.Cast <ListItem>().Where(it => it.Selected == true).Select(it => it.Value);
if (beatcodelist.Count() > 0)
{
beatcode = string.Join(",", beatcodelist);
}
//foreach (System.Web.UI.WebControls.ListItem litem in drpBEAT.Items)
//{
// if (litem.Selected)
// {
// if (beatcode.Length == 0)
// beatcode = "" + litem.Value.ToString() + "";
// else
// beatcode += "," + litem.Value.ToString() + "";
// }
//}
string siteid = "";
var siteidlist = ddlSiteId.Items.Cast <ListItem>().Where(it => it.Selected == true).Select(it => it.Value);
if (siteidlist.Count() > 0)
{
siteid = string.Join(",", siteidlist);
}
//foreach (System.Web.UI.WebControls.ListItem litem in ddlSiteId.Items)
//{
// if (litem.Selected)
// {
// if (siteid.Length == 0)
// siteid = "" + litem.Value.ToString() + "";
// else
// siteid += "," + litem.Value.ToString() + "";
// }
//}
string rtlstate = "";
var rtlstatelist = drpStatus.Items.Cast <ListItem>().Where(it => it.Selected == true).Select(it => it.Value);
if (rtlstatelist.Count() > 0)
{
rtlstate = string.Join(",", rtlstatelist);
}
//foreach (System.Web.UI.WebControls.ListItem litem in drpStatus.Items)
//{
// if (litem.Selected)
// {
// if (rtlstate.Length == 0)
// rtlstate = "" + litem.Value.ToString() + "";
// else
// rtlstate += "," + litem.Value.ToString() + "";
// }
//}
//string query = " SELECT SiteCode[Distributor Code],D.Name[Distributor Name],E.ROLEDESCRIPTION[Distributor Type],h.PSR_Code[PSR Code],h.PSR_Name[PSR Name],g.BeatCode[Beat Code],g.BeatName[Beat Name],c.CUSTOMER_CODE[Customer Code],replace(C.CUSTOMER_NAME, '''', '') [CUSTOMER NAME],CONVERT(NVARCHAR(11), a.StatusDate, 106)[Status Date],B.ShopStatus_Name[Shop Status]" +
// " FROM AX.ACXShopStatusEntry A" +
// " LEFT JOIN AX.ACXShopStatus B ON A.StatusCode = B.ShopStatus_Code" +
// " LEFT JOIN AX.ACXCUSTMASTER C ON A.CustomerCode = C.CUSTOMER_CODE" +
// " LEFT JOIN AX.InventSite D ON A.SiteCode = D.SiteID" +
// " left join ax.acxusermaster E on A.SiteCode=E.Site_Code"+
// " left join ax.acxpsrbeatmaster g on PSR_CODE =g.PSRCode"+
// " left join ax.acxpsrmaster h on g.PSRCode =h.PSR_Code"+
// " and C.PSR_BEAT = g.BeatCode"+
// " WHERE A.StatusDate >= '" + frmDate + "' AND A.StatusDate <= '" + endDate + "' ";
conn = baseObj.GetConnection();
cmd = new SqlCommand("[dbo].[USP_StatusReport]");
cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@FDate", frmDate);
cmd.Parameters.AddWithValue("@TDate", endDate);
cmd.Parameters.AddWithValue("@SiteID", siteid);
cmd.Parameters.AddWithValue("@psrcode", psrcode);
cmd.Parameters.AddWithValue("@beatcode", beatcode);
cmd.Parameters.AddWithValue("@statusshp", rtlstate);
DataTable dtFilter = new DataTable();
dtFilter.Load(cmd.ExecuteReader());
if (dtFilter.Rows.Count > 0)
{
ViewState["temp"] = dtFilter;
GridView2.DataSource = dtFilter;
GridView2.DataBind();
GridView2.HeaderRow.TableSection = TableRowSection.TableHeader;
foreach (GridViewRow grv in GridView2.Rows)
{
CheckBox chkAll = (CheckBox)grv.Cells[0].FindControl("chklist");
chkAll.Checked = true;
}
uppanel.Update();
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Alert", " alert('No Data Exist !! ');", true);
GridView2.DataSource = null;
GridView2.DataBind();
}
}
protected void btnSubmit_Click1(object sender, EventArgs e)
{
SqlConnection con = baseObj.GetConnection();
SqlCommand cmd = new SqlCommand();
SqlTransaction tran = null;
try
{
if (grvDetail.Rows.Count <= 0)
{
this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('No Line Exist in GridView...!');", true);
return;
}
DataTable dt = baseObj.GetNumSequenceNew(11, Session["SiteCode"].ToString(), Session["DATAAREAID"].ToString()); //st.Substring(st.Length - 6) + System.DateTime.Now.ToString("yyMMddhhmmss");
string Doc_NO = string.Empty;
string NUMSEQ = string.Empty;
if (dt != null)
{
Doc_NO = dt.Rows[0][0].ToString();
NUMSEQ = dt.Rows[0][1].ToString();
}
else
{
return;
}
if (con.State.ToString() == "Closed")
{
con.Open();
}
tran = con.BeginTransaction();
cmd.Connection = con;
cmd.Transaction = tran;
foreach (GridViewRow grv in grvDetail.Rows)
{
string OBJECT_CODE = grv.Cells[8].Text;
string OBJECT_SUBCODE = grv.Cells[7].Text;
string TARGET_CODE = "";
string OBJECT = "";
string TARGET_DESCRIPTION = grv.Cells[10].Text;
string FROM_DATE = grv.Cells[0].Text;
string TO_DATE = grv.Cells[1].Text;
string TARGET = "0";
string ACHIVEMENT = "0";
string TARGET_INCENTIVE = "0";
string ACTUAL_INCENTIVE = grv.Cells[11].Text;
string CLAIM_CATEGORY = grv.Cells[2].Text;
string CLAIM_SUBCATEGORY = grv.Cells[4].Text;
string ClaimType = "0";
HiddenField hdnBUCode = (HiddenField)grv.FindControl("BUCode");
string BU_CODE = hdnBUCode.Value.ToString();
if (grv.Cells[6].Text == "PSR")
{
ClaimType = "0";
}
if (grv.Cells[6].Text == "SITE")
{
ClaimType = "1";
}
if (grv.Cells[6].Text == "CUSTOMERGROUP")
{
ClaimType = "2";
}
string CLAIM_TYPE = ClaimType;
string STATUS = "0";
cmd.Connection = con;
cmd.Parameters.Clear();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ACXINSERTCLAIMMASTER";
cmd.Parameters.Add("@DATAAREAID", SqlDbType.NVarChar).Value = Session["DATAAREAID"];
cmd.Parameters.Add("@DOCUMENT_CODE", SqlDbType.NVarChar).Value = Doc_NO;
cmd.Parameters.Add("@SITE_CODE", SqlDbType.NVarChar).Value = Session["SiteCode"].ToString();
cmd.Parameters.Add("@OBJECT", SqlDbType.NVarChar).Value = OBJECT;
cmd.Parameters.Add("@OBJECT_CODE", SqlDbType.NVarChar).Value = OBJECT_CODE;
cmd.Parameters.Add("@OBJECT_SUBCODE", SqlDbType.NVarChar).Value = OBJECT_SUBCODE;
cmd.Parameters.Add("@TARGET_CODE", SqlDbType.NVarChar).Value = TARGET_CODE;
cmd.Parameters.Add("@TARGET_DESCRIPTION", SqlDbType.NVarChar).Value = TARGET_DESCRIPTION;
cmd.Parameters.Add("@FROM_DATE", SqlDbType.SmallDateTime).Value = FROM_DATE;
cmd.Parameters.Add("@TO_DATE", SqlDbType.SmallDateTime).Value = TO_DATE;
cmd.Parameters.Add("@TARGET", SqlDbType.Decimal).Value = TARGET;
cmd.Parameters.Add("@ACHIVEMENT", SqlDbType.Decimal).Value = ACHIVEMENT;
cmd.Parameters.Add("@TARGET_INCENTIVE", SqlDbType.Decimal).Value = TARGET_INCENTIVE;
cmd.Parameters.Add("@ACTUAL_INCENTIVE", SqlDbType.Decimal).Value = ACTUAL_INCENTIVE;
cmd.Parameters.Add("@CLAIM_CATEGORY", SqlDbType.NVarChar).Value = CLAIM_CATEGORY;
cmd.Parameters.Add("@CLAIM_SUBCATEGORY", SqlDbType.NVarChar).Value = CLAIM_SUBCATEGORY;
cmd.Parameters.Add("@CLAIM_TYPE", SqlDbType.NVarChar).Value = CLAIM_TYPE;
cmd.Parameters.Add("@STATUS", SqlDbType.Int).Value = STATUS;
cmd.Parameters.Add("@NumSeq", SqlDbType.BigInt).Value = NUMSEQ;
cmd.Parameters.Add("@TARGET_GROUP", SqlDbType.NVarChar).Value = "";
cmd.Parameters.Add("@CALCULATION_PATTERN", SqlDbType.NVarChar).Value = "";
cmd.Parameters.Add("CLAIM_MONTH", SqlDbType.NVarChar).Value = FROM_DATE;
cmd.Parameters.Add("@BU_CODE", SqlDbType.NVarChar).Value = BU_CODE;
cmd.ExecuteNonQuery();
// string sqlstr = @"INSERT INTO [ax].[ACXCLAIMMASTER] ([DATAAREAID],[RECID]
// ,[DOCUMENT_CODE],[DOCUMENT_DATE],[SITE_CODE],[OBJECT],[OBJECT_CODE],[OBJECT_SUBCODE],[TARGET_CODE]
// ,[TARGET_DESCRIPTION],[FROM_DATE],[TO_DATE],[TARGET],[ACHIVEMENT],[TARGET_INCENTIVE],[ACTUAL_INCENTIVE],[CLAIM_CATEGORY]
// ,[CLAIM_SUBCATEGORY],[CLAIM_TYPE],[STATUS],[NumSeq],[Target_Group],[Calculation_Pattern])
// VALUES ( '" + Session["DATAAREAID"] + "','" + Recid + "','" + Doc_NO + "','" + System.DateTime.Now + "','" + Session["SiteCode"].ToString() + "','" + CLAIM_TYPE + "','" + OBJECT_CODE + "', " +
// " '" + OBJECT_SUBCODE + "', '" + TARGET_CODE + "','" + TARGET_DESCRIPTION + "','" + FROM_DATE + "','" + TO_DATE + "','" + TARGET + "','" + ACHIVEMENT + "', " +
// " '" + TARGET_INCENTIVE + "','" + ACTUAL_INCENTIVE + "','" + CLAIM_CATEGORY + "','" + CLAIM_SUBCATEGORY + "','0','" + STATUS + "','" + NUMSEQ + "','','')";
// cmd.Connection = con;
// cmd.CommandText = sqlstr;
// cmd.ExecuteNonQuery();
}
tran.Commit();
//string message = "alert('Data Saved Successfully : " + Doc_NO + "')";
//ScriptManager.RegisterClientScriptBlock((sender as Control), this.GetType(), "alert", message, true);
//this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Data Saved Successfully : " + Doc_NO + "');", true);
Cleardata();
grvDetail.DataSource = null;
grvDetail.DataBind();
grvDetail.Visible = false;
creataDataTabel();
updatepnlGrid.Update();
string message = "alert('Data Saved Successfully : " + Doc_NO + "')";
ScriptManager.RegisterClientScriptBlock((sender as Control), this.GetType(), "alert", message, true);
}
catch (Exception ex)
{
tran.Rollback();
lblError.Text = ex.Message;
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally { if (con.State.ToString() == "Open")
{
con.Close();
}
}
}
public void SaveDetails()
{
try
{
dt = new DataTable();
//============Generate Doc Number=============
query = "SELECT ISNULL(MAX(CAST(RIGHT([Document_No],6) AS INT)),0)+1 as [Document_No] FROM [ax].[ACXCOLLECTIONENTRY] where [SiteCode]='" + Session["SiteCode"].ToString() + "' and [DATAAREAID]='" + Session["DATAAREAID"].ToString() + "'";
dt = baseObj.GetData(query);
int vc = Convert.ToInt32(dt.Rows[0]["Document_No"].ToString());
//string DocumentNo = obj.GetNumSequence(8, Session["SiteCode"].ToString(), Session["DATAAREAID"].ToString());
DataTable dtNumSeq = obj.GetNumSequenceNew(8, Session["SiteCode"].ToString(), Session["DATAAREAID"].ToString()); //st.Substring(st.Length - 6) + System.DateTime.Now.ToString("yyMMddhhmmss");
string NUMSEQ = string.Empty;
string DocumentNo = string.Empty;
if (dtNumSeq != null)
{
DocumentNo = dtNumSeq.Rows[0][0].ToString();
NUMSEQ = dtNumSeq.Rows[0][1].ToString();
}
else
{
return;
}
conn = baseObj.GetConnection();
cmd = new SqlCommand("ACX_CollectionEntry");
transaction = conn.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = transaction;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
cmd1 = new SqlCommand("ACX_CollectionEntry");
cmd1.Connection = conn;
cmd1.Transaction = transaction;
cmd1.CommandTimeout = 0;
cmd1.CommandType = CommandType.StoredProcedure;
//======Save CollectionEntry===================
//cmd.CommandText = string.Empty;
//cmd.CommandType = CommandType.StoredProcedure;
//cmd = new SqlCommand("ACX_CollectionEntry");
int i = 0;
foreach (GridViewRow gv in gvDetails.Rows)
{
i = i + 1;
HiddenField Customercode = (HiddenField)gv.Cells[0].FindControl("HiddenField2");
DropDownList InstrumentCode = (DropDownList)gv.Cells[3].FindControl("drpInstrument");
TextBox InstrumentNo = (TextBox)gv.Cells[4].FindControl("txtInstrument");
DropDownList RefDocument_No = (DropDownList)gv.Cells[5].FindControl("drpRefDocument");
Label RefDocument_Date = (Label)gv.Cells[6].FindControl("lblRefDocDate");
Label PendingAmount = (Label)gv.Cells[7].FindControl("lblPendingAmount");
TextBox Amount = (TextBox)gv.Cells[8].FindControl("txtAmount");
//TextBox Remark = (TextBox)gv.Cells[9].FindControl("txtRemark");
TextBox Remark = gv.FindControl("txtRemark") as TextBox;
decimal amount, pendingamount;
if (Amount.Text == string.Empty)
{
amount = 0;
}
else
{
amount = Convert.ToDecimal(Amount.Text);
}
if (PendingAmount.Text == string.Empty)
{
pendingamount = 0;
}
else
{
pendingamount = Convert.ToDecimal(PendingAmount.Text);
}
if (amount != 0)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@SiteCode", Session["SiteCode"].ToString());
cmd.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
cmd.Parameters.AddWithValue("@Customer_Code", Customercode.Value);
cmd.Parameters.AddWithValue("@Document_No", DocumentNo);
cmd.Parameters.AddWithValue("@Instrument_Code", InstrumentCode.SelectedItem.Value);
cmd.Parameters.AddWithValue("@Instrument_No", InstrumentNo.Text);
cmd.Parameters.AddWithValue("@Ref_DocumentNo", RefDocument_No.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Ref_DocumentDate", RefDocument_Date.Text);
cmd.Parameters.AddWithValue("@Collection_Amount", amount);
cmd.Parameters.AddWithValue("@Collection_Date", txtCollectionDate.Text);
cmd.Parameters.AddWithValue("@Status", "INSERT");
cmd.Parameters.AddWithValue("@NUMSEQ", NUMSEQ);
cmd.Parameters.AddWithValue("@Remark", Remark.Text.Trim());
cmd.ExecuteNonQuery();
//===============Update Customer Ledger/Transaction Table===============
cmd1.Parameters.Clear();
cmd1.Parameters.AddWithValue("@SiteCode", Session["SiteCode"].ToString());
cmd1.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
cmd1.Parameters.AddWithValue("@Customer_Code", Customercode.Value);
cmd1.Parameters.AddWithValue("@Document_No", RefDocument_No.SelectedItem.Text);
cmd1.Parameters.AddWithValue("@RemainingAmount", pendingamount - amount);
cmd1.Parameters.AddWithValue("@Status", "UPDATE");
cmd1.Parameters.AddWithValue("@Remark", Remark.Text.Trim());
cmd1.ExecuteNonQuery();
//obj.UpdateLastNumSequence(8, Session["SiteCode"].ToString(), Session["DATAAREAID"].ToString(), conn, transaction);
//============Commit All Data================
}
else
{
}
}
transaction.Commit();
this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Collection Entry Document No : " + DocumentNo + " Generated Successfully.!');", true);
BindGridview();
}
catch (Exception ex)
{
transaction.Rollback();
//this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Please Try Again!!');", true);
LblMessage.Text = "'Please Try Again!! !";
LblMessage.Visible = true;
uppanel.Update();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
conn.Close();
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
if (gvDetails.Rows.Count > 0)
{
Button btn = sender as Button;
GridViewRow row = btn.NamingContainer as GridViewRow;
// string pk = gvDetails.DataKeys[row.RowIndex].Values[0].ToString();
// string strInvoiceNo = string.Empty;
foreach (GridViewRow gv in gvDetails.Rows)
{
DataTable dt = new DataTable();
//============Generate Doc Number=============
query = "SELECT ISNULL(MAX(CAST(RIGHT([Document_No],6) AS INT)),0)+1 as [Document_No] FROM [ax].[ACXCOLLECTIONENTRY] where [SiteCode]='" + Session["SiteCode"].ToString() + "' and [DATAAREAID]='" + Session["DATAAREAID"].ToString() + "'";
dt = baseObj.GetData(query);
int vc = Convert.ToInt32(dt.Rows[0]["Document_No"].ToString());
DataTable dtNumSeq = baseObj.GetNumSequenceNew(8, Session["SiteCode"].ToString(), Session["DATAAREAID"].ToString()); //st.Substring(st.Length - 6) + System.DateTime.Now.ToString("yyMMddhhmmss");
string NUMSEQ = string.Empty;
string DocumentNo = string.Empty;
if (dtNumSeq != null)
{
DocumentNo = dtNumSeq.Rows[0][0].ToString();
NUMSEQ = dtNumSeq.Rows[0][1].ToString();
}
else
{
return;
}
conn = baseObj.GetConnection();
cmd = new SqlCommand("ACX_CollectionEntry");
transaction = conn.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = transaction;
cmd.CommandTimeout = 3600;
cmd.CommandType = CommandType.StoredProcedure;
cmd1 = new SqlCommand("ACX_CollectionEntry");
cmd1.Connection = conn;
cmd1.Transaction = transaction;
cmd1.CommandTimeout = 3600;
cmd1.CommandType = CommandType.StoredProcedure;
decimal amount = 0;
CheckBox chkbox = (CheckBox)gv.FindControl("chkInvoice");
HiddenField strInvoice_No = (HiddenField)gv.FindControl("hdInvoice_No");
if (chkbox.Checked == true)
{
try
{
Label lblRemainingAmount = (Label)gv.FindControl("lblRemainingAmount");
Label lblCUSTOMER_CODE = (Label)gv.FindControl("lblCUSTOMER_CODE");
Label lblDocument_Date = (Label)gv.FindControl("lblDocument_Date");
amount = Convert.ToDecimal(lblRemainingAmount.Text.Trim());
if (amount > 0)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@SiteCode", Session["SiteCode"].ToString());
cmd.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
cmd.Parameters.AddWithValue("@Customer_Code", lblCUSTOMER_CODE.Text.Trim());
cmd.Parameters.AddWithValue("@Document_No", DocumentNo);
cmd.Parameters.AddWithValue("@Instrument_Code", "CASH");
cmd.Parameters.AddWithValue("@Instrument_No", "");
cmd.Parameters.AddWithValue("@Ref_DocumentNo", strInvoice_No.Value);
cmd.Parameters.AddWithValue("@Ref_DocumentDate", Convert.ToDateTime(lblDocument_Date.Text));
cmd.Parameters.AddWithValue("@Collection_Amount", amount);
cmd.Parameters.AddWithValue("@Collection_Date", DateTime.Now);
cmd.Parameters.AddWithValue("@Status", "INSERT");
cmd.Parameters.AddWithValue("@NUMSEQ", NUMSEQ);
cmd.Parameters.AddWithValue("@Remark", "WRITE OFF");
cmd.ExecuteNonQuery();
//===============Update Customer Ledger/Transaction Table===============
cmd1.Parameters.Clear();
cmd1.Parameters.AddWithValue("@SiteCode", Session["SiteCode"].ToString());
cmd1.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
cmd1.Parameters.AddWithValue("@Customer_Code", lblCUSTOMER_CODE.Text.Trim());
cmd1.Parameters.AddWithValue("@Document_No", strInvoice_No.Value);
cmd1.Parameters.AddWithValue("@RemainingAmount", 0);
cmd1.Parameters.AddWithValue("@Status", "UPDATE");
cmd1.Parameters.AddWithValue("@Remark", "WRITE OFF");
cmd1.ExecuteNonQuery();
//obj.UpdateLastNumSequence(8, Session["SiteCode"].ToString(), Session["DATAAREAID"].ToString(), conn, transaction);
//============Commit All Data================
transaction.Commit();
}
else
{
}
//
}
catch (Exception ex)
{
transaction.Rollback();
//this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Please Try Again!!');", true);
LblMessage.Text = "'Please Try Again!! !";
LblMessage.Visible = true;
uppanel.Update();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
conn.Close();
}
}
}
BtnShowReport0_Click(null, null);
this.Page.ClientScript.RegisterStartupScript(GetType(), "Alert", " alert('Generated Successfully.!');", true);
}
}
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;
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 = 3600;
cmd.CommandType = CommandType.StoredProcedure;
query = "[ax].[ACX_VRS_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");
}
}
cmd.Parameters.AddWithValue("@VRSCode", ddlVRS.SelectedValue.ToString());
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 = 3600;
cmd1.CommandType = CommandType.StoredProcedure;
query = "[ax].[ACX_VRS_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", "");
}
cmd1.Parameters.AddWithValue("@VRSCode", ddlVRS.SelectedValue.ToString());
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();
}
}
protected void BtnSave_Click(object sender, EventArgs e)
{
try
{
LblMessage.Text = "";
bool b = Validation();
for (int i = 0; i < gvDetails.Rows.Count; i++)
{
TextBox txtBoxQty = (TextBox)gvDetails.Rows[i].Cells[4].FindControl("txtQty");
if (txtBoxQty.Text == "")
{
string message = "alert('Qty cannot be left blank or Zero..!!');";
ScriptManager.RegisterClientScriptBlock((sender as Control), this.GetType(), "alert", message, true);
b = false;
return;
}
else if (Convert.ToDecimal(txtBoxQty.Text) < 0)
{
string message = "alert('Qty must be greater than or equal to zero..!!');";
ScriptManager.RegisterClientScriptBlock((sender as Control), this.GetType(), "alert", message, true);
b = false;
return;
}
}
if (b == true)
{
conn = obj.GetConnection();
string strCode = string.Empty;
cmd = new SqlCommand();
transaction = conn.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = transaction;
cmd.CommandTimeout = 3600;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ACX_INVOICE_NSSTOCK_TRANSFER";
Int32 Movetype;
Movetype = rdprimarytononsalable.Checked == true ? 1 : 0;
string fromlocation, Tolocation;
fromlocation = Tolocation = "";
string query1;
DataTable dtloc = new DataTable();
query1 = "select MainWarehouse from ax.inventsite where siteid='" + Session["SiteCode"].ToString() + "' ";
dtloc = obj.GetData(query1);
if (dtloc.Rows.Count > 0)
{
if (Movetype == 1)
{
fromlocation = dtloc.Rows[0]["MainWarehouse"].ToString();
}
else
{
Tolocation = dtloc.Rows[0]["MainWarehouse"].ToString();
}
}
query1 = "Select INVENTLOCATIONID from Ax.inventlocation where InventSiteid = '" + Session["SiteCode"].ToString() + "' and ACX_WAREHOUSETYPE = 1";
dtloc = obj.GetData(query1);
if (dtloc.Rows.Count > 0)
{
if (Movetype == 1)
{
Tolocation = dtloc.Rows[0]["INVENTLOCATIONID"].ToString();
}
else
{
fromlocation = dtloc.Rows[0]["INVENTLOCATIONID"].ToString();
}
}
for (int i = 0; i < gvDetails.Rows.Count; i++)
{
TextBox txtBoxQty = (TextBox)gvDetails.Rows[i].Cells[4].FindControl("txtQty");
string strqty = txtBoxQty.Text;
if (Convert.ToDecimal(txtBoxQty.Text) > 0)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@SiteCode", Session["SiteCode"].ToString());
cmd.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
cmd.Parameters.AddWithValue("@TransType", Movetype.ToString());
string productNameCode = gvDetails.Rows[i].Cells[2].Text;
string[] str = productNameCode.Split('-');
Label lbluom = (Label)gvDetails.Rows[i].Cells[7].FindControl("UT");
cmd.Parameters.AddWithValue("@ProductCode", str[0].ToString());
cmd.Parameters.AddWithValue("@TransQty", Convert.ToDecimal(strqty).ToString());
cmd.Parameters.AddWithValue("@TransUOM", gvDetails.Rows[i].Cells[8].Text.ToString());
cmd.Parameters.AddWithValue("@TransFromLocation", fromlocation.ToString());
cmd.Parameters.AddWithValue("@TransToLocation", Tolocation.ToString());
cmd.Parameters.AddWithValue("@Referencedocumentno", ddlInvoceNo.Text.ToString());
cmd.ExecuteNonQuery();
}
}
transaction.Commit();
//transaction.Rollback();
ClearAll();
LblMessage.Text = "Stock Moved Successfully ..!";
}
}
catch (Exception ex)
{
transaction.Rollback();
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 = "[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 = 3600;
cmd.CommandType = CommandType.StoredProcedure;
//query = "dbo.ACX_USP_TargetAchivementReport";
//cmd.CommandText = query;
cmd.CommandText = "dbo.ACX_USP_TargetAchivementReport";
cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(txtFromDate.Text));
cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text));
cmd.Parameters.AddWithValue("@UserType", Convert.ToString(Session["LOGINTYPE"]));
cmd.Parameters.AddWithValue("@UserCode", Convert.ToString(Session["USERID"]));
// state
if (ddlState.SelectedIndex < 0)
{
cmd.Parameters.AddWithValue("@StateId", "");
}
if (ddlState.SelectedIndex > 0)
{
cmd.Parameters.AddWithValue("@StateId", ddlState.SelectedItem.Value);
}
else if (ddlState.SelectedItem.Text != "All...")
{
cmd.Parameters.AddWithValue("@StateId", ddlState.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@StateId", "");
}
// 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);
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 (drpCategory.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@TargetCategory", drpCategory.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@TargetCategory", "");
}
if (drpSubCategory.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@TargetSubCategory", drpSubCategory.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@TargetSubCategory", "");
}
if (drpCustGroup.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@ObjectCode", drpCustGroup.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@ObjectCode", "");
}
if (drpCustomerName.SelectedIndex >= 1)
{
cmd.Parameters.AddWithValue("@targetsubobject", drpCustomerName.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@targetsubobject", "");
}
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();
//if (conn != null)
//{
// if (conn.State.ToString() == "Open") { conn.Close(); }
//}
}
}
protected void btnExcelUpload_Click(object sender, EventArgs e)
{
//btnExcelUpload.Attributes.Add("onclick", "javascript: if (Page_ClientValidate() ){" + btnExcelUpload.ClientID + ".disabled=true;}" + ClientScript.GetPostBackEventReference(btnExcelUpload, ""));
SqlTransaction tran = null;
string query = string.Empty;
SqlConnection conn = baseObj.GetConnection();
SqlCommand cmd = new SqlCommand("USP_EXCELOPENINGSTOCKUPLOAD");
tran = conn.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
if (conn == null)
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "Validate", "alert('Connection lost. Please check connection.');", true);
return;
}
try
{
int no = 0;
if (AsyncFileUpload1.HasFile)
{
// #region
string fileName = System.IO.Path.GetFileName(AsyncFileUpload1.PostedFile.FileName);
AsyncFileUpload1.PostedFile.SaveAs(Server.MapPath("~/Uploads/" + fileName));
string excelPath = Server.MapPath("~/Uploads/") + Path.GetFileName(AsyncFileUpload1.PostedFile.FileName);
string conString = string.Empty;
string extension = Path.GetExtension(AsyncFileUpload1.PostedFile.FileName);
DataTable dtExcelData = new DataTable();
//excel upload
try {
dtExcelData = CreamBell_DMS_WebApps.App_Code.ExcelUpload.ImportExcelXLS(Server.MapPath("~/Uploads/" + fileName), true);
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "Execl Upload", "alert('" + ex.Message.ToString().Replace("'", "''") + "');", true);
lblError.Text = ex.Message;
lblError.ForeColor = System.Drawing.Color.Red;
ErrorSignal.FromCurrentContext().Raise(ex);
return;
}
DataSet lds = new DataSet();
DataTable dt = new DataTable();
//lds.Tables.Add(dtForShownUnuploadData);
dtExcelData.TableName = "OpeningStockUpload";
lds.Tables.Add(dtExcelData);
RemoveTimezoneForDataSet(lds);
string ls_xml = lds.GetXml();
SqlParameter pvNewId = new SqlParameter();
pvNewId.ParameterName = "@SuccessCount";
pvNewId.DbType = DbType.Int32;
pvNewId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(pvNewId);
cmd.Parameters.AddWithValue("@XmlData", ls_xml);
cmd.Parameters.AddWithValue("@DataAreaId", Session["DATAAREAID"].ToString());
dt.Load(cmd.ExecuteReader());
if (dt.Rows.Count > 0)
{
gridviewRecordNotExist.DataSource = dt;
gridviewRecordNotExist.DataBind();
ModalPopupExtender1.Show();
}
lblUpload.Text = cmd.Parameters["@SuccessCount"].Value.ToString() + " Records Uploaded Successfully...";
lblUpload.ForeColor = System.Drawing.Color.Green;
tran.Commit();
//DataTable dtForShownUnuploadData = new DataTable();
//dtForShownUnuploadData.Columns.Add("Site Code");
//dtForShownUnuploadData.Columns.Add("Product Code");
//dtForShownUnuploadData.Columns.Add("Box Qty");
//dtForShownUnuploadData.Columns.Add("Crate Qty");
//dtForShownUnuploadData.Columns.Add("Rejected Reason");
//string DATAAREAID = Session["DATAAREAID"].ToString();
//int TransType = 7;//1 for Adj
//int DocumentType = 7;
//string DocumentNo = "";
//string Referencedocumentno = "";
//string TransLocation = "";
// tran = conn.BeginTransaction();
// cmd.Transaction = tran;
// cmd.Connection = conn;
// int j = 0;
// for (int k = 0; k < dtExcelData.Rows.Count; k++)
// {
// if (dtExcelData.Rows[k]["Site Code"].ToString() == "")
// {
// break;
// }
// //If Same Site , Same Transaction Type for any Item exists, System will not process the Excel Upload activity.
// string sqlstr = " select top 1 SITECODE from [ax].[ACXINVENTTRANS] where SITECODE = '" + dtExcelData.Rows[k]["Site Code"].ToString().TrimEnd() + "' "
// + " and TRANSTYPE = '7' and ProductCode ='" + dtExcelData.Rows[k]["Product Code"].ToString() + "' ";
// object objcheck = baseObj.GetScalarValue(sqlstr);
// if (objcheck != null)
// {
// lblEror1.Text = "Record Already Exist,System will not process the Excel Upload activity";
// lblEror1.ForeColor = System.Drawing.Color.Red;
// lblError.Visible = false;
// dtForShownUnuploadData.Rows.Add();
// dtForShownUnuploadData.Rows[j]["Site Code"] = dtExcelData.Rows[k]["Site Code"].ToString();
// dtForShownUnuploadData.Rows[j]["Product Code"] = dtExcelData.Rows[k]["Product Code"].ToString();
// dtForShownUnuploadData.Rows[j]["Box Qty"] = dtExcelData.Rows[k]["Box Qty"].ToString();
// dtForShownUnuploadData.Rows[j]["Crate Qty"] = dtExcelData.Rows[k]["Crate Qty"].ToString();
// dtForShownUnuploadData.Rows[j]["Rejected Reason"] = "Record Already Exist..";
// j += 1;
// }
// //check for SiteCode
// sqlstr = "select top 1 SITEID from ax.InventSite where SITEID = '" + dtExcelData.Rows[k]["Site Code"].ToString().TrimEnd() + "' ";
// objcheck = baseObj.GetScalarValue(sqlstr);
// if (objcheck == null)
// {
// dtForShownUnuploadData.Rows.Add();
// dtForShownUnuploadData.Rows[j]["Site Code"] = dtExcelData.Rows[k]["Site Code"].ToString();
// dtForShownUnuploadData.Rows[j]["Product Code"] = dtExcelData.Rows[k]["Product Code"].ToString();
// dtForShownUnuploadData.Rows[j]["Box Qty"] = dtExcelData.Rows[k]["Box Qty"].ToString();
// dtForShownUnuploadData.Rows[j]["Crate Qty"] = dtExcelData.Rows[k]["Crate Qty"].ToString();
// dtForShownUnuploadData.Rows[j]["Rejected Reason"] = "Site Id Not Exist..";
// j += 1;
// continue;
// }
// //check for Transactionlocation id
// string query1 = "select MainWarehouse from ax.inventsite where siteid='" + dtExcelData.Rows[k]["Site Code"].ToString() + "'";
// object objTranslocation = baseObj.GetScalarValue(query1);
// if (objTranslocation != null)
// {
// TransLocation = objTranslocation.ToString();
// }
// else
// {
// dtForShownUnuploadData.Rows.Add();
// dtForShownUnuploadData.Rows[j]["Site Code"] = dtExcelData.Rows[k]["Site Code"].ToString();
// dtForShownUnuploadData.Rows[j]["Product Code"] = dtExcelData.Rows[k]["Product Code"].ToString();
// dtForShownUnuploadData.Rows[j]["Box Qty"] = dtExcelData.Rows[k]["Box Qty"].ToString();
// dtForShownUnuploadData.Rows[j]["Crate Qty"] = dtExcelData.Rows[k]["Crate Qty"].ToString();
// dtForShownUnuploadData.Rows[j]["Rejected Reason"] = "TransLocation ID not Found";
// j += 1;
// continue;
// }
// //check for Product Code
// sqlstr = "select top 1 ItemID from ax.inventTable where ItemID = '" + dtExcelData.Rows[k]["Product Code"].ToString() + "'";
// object objcheckproductcode = baseObj.GetScalarValue(sqlstr);
// if (objcheckproductcode == null)
// {
// dtForShownUnuploadData.Rows.Add();
// dtForShownUnuploadData.Rows[j]["Site Code"] = dtExcelData.Rows[k]["Site Code"].ToString();
// dtForShownUnuploadData.Rows[j]["Product Code"] = dtExcelData.Rows[k]["Product Code"].ToString();
// dtForShownUnuploadData.Rows[j]["Box Qty"] = dtExcelData.Rows[k]["Box Qty"].ToString();
// dtForShownUnuploadData.Rows[j]["Crate Qty"] = dtExcelData.Rows[k]["Crate Qty"].ToString();
// dtForShownUnuploadData.Rows[j]["Rejected Reason"] = "Product Code Not Exist..";
// j += 1;
// continue;
// }
// //check for Product Code is Blocked
// sqlstr = "select top 1 ItemID from ax.inventTable where BLOCK=0 and ItemID = '" + dtExcelData.Rows[k]["Product Code"].ToString() + "'";
// object objcheckblockproductcode = baseObj.GetScalarValue(sqlstr);
// if (objcheckblockproductcode == null)
// {
// dtForShownUnuploadData.Rows.Add();
// dtForShownUnuploadData.Rows[j]["Site Code"] = dtExcelData.Rows[k]["Site Code"].ToString();
// dtForShownUnuploadData.Rows[j]["Product Code"] = dtExcelData.Rows[k]["Product Code"].ToString();
// dtForShownUnuploadData.Rows[j]["Box Qty"] = dtExcelData.Rows[k]["Box Qty"].ToString();
// dtForShownUnuploadData.Rows[j]["Crate Qty"] = dtExcelData.Rows[k]["Crate Qty"].ToString();
// dtForShownUnuploadData.Rows[j]["Rejected Reason"] = "Product Code Is Blocked..";
// j += 1;
// continue;
// }
// sqlstr = "select top 1 ITEMID from ax.inventTable where BU_CODE in (select BU_CODE from ax.acxsitebumapping where SITEID='" + dtExcelData.Rows[k]["Site Code"].ToString() + "') and ITEMID = '" + dtExcelData.Rows[k]["Product Code"].ToString() + "' and BLOCK=0 ";
// object objcheckBUCode = baseObj.GetScalarValue(sqlstr);
// if (objcheckBUCode == null)
// {
// dtForShownUnuploadData.Rows.Add();
// dtForShownUnuploadData.Rows[j]["Site Code"] = dtExcelData.Rows[k]["Site Code"].ToString();
// dtForShownUnuploadData.Rows[j]["Product Code"] = dtExcelData.Rows[k]["Product Code"].ToString();
// dtForShownUnuploadData.Rows[j]["Box Qty"] = dtExcelData.Rows[k]["Box Qty"].ToString();
// dtForShownUnuploadData.Rows[j]["Crate Qty"] = dtExcelData.Rows[k]["Crate Qty"].ToString();
// dtForShownUnuploadData.Rows[j]["Rejected Reason"] = "Product Code Does Not Belong To BU..";
// j += 1;
// continue;
// }
// string Siteid = dtExcelData.Rows[k]["Site Code"].ToString().TrimEnd();
// string TransId = string.Empty;
// if (Siteid.Length >= 6)
// {
// TransId = Siteid.Substring(Siteid.Length - 6) + System.DateTime.Now.ToString("yymmddhhmmss");
// }
// else
// {
// TransId = Siteid + System.DateTime.Now.ToString("yymmddhhmmss");
// }
// string Product = dtExcelData.Rows[k]["Product Code"].ToString().TrimEnd();
// string Box = dtExcelData.Rows[k]["Box Qty"].ToString().TrimEnd();
// string Crate = dtExcelData.Rows[k]["Crate Qty"].ToString().TrimEnd();
// decimal PackSize;
// PackSize = Convert.ToDecimal(baseObj.GetScalarValue("SELECT ISNULL(i.PRODUCT_CRATE_PACKSIZE,1) FROM ax.INVENTTABLE i WHERE i.ITEMID='" + dtExcelData.Rows[k]["Product Code"].ToString().TrimEnd() + "'"));
// decimal TransQty = Convert.ToDecimal(Box) + (Convert.ToDecimal(Crate) * PackSize);
// int REcid = k + 1;
// if (TransQty > 0)
// {
// cmd.CommandText = "USP_OPENSTOCKTOINVENTTRANS";
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.CommandTimeout = 100;
// cmd.Parameters.Clear();
// cmd.Parameters.AddWithValue("@TransId", TransId.ToString());
// cmd.Parameters.AddWithValue("@Siteid", Siteid.ToString());
// cmd.Parameters.AddWithValue("@Dataareaid", DATAAREAID.ToString());
// cmd.Parameters.AddWithValue("@TransType", TransType.ToString());
// cmd.Parameters.AddWithValue("@DocumentType", DocumentType.ToString());
// cmd.Parameters.AddWithValue("@DocumentNo", DocumentNo.ToString());
// cmd.Parameters.AddWithValue("@DocumentDate", System.DateTime.Now.ToString());
// cmd.Parameters.AddWithValue("@ProductCode", Product.ToString());
// cmd.Parameters.AddWithValue("@TransQty", TransQty.ToString());
// cmd.Parameters.AddWithValue("@TransUOM", "BOX");
// cmd.Parameters.AddWithValue("@TransLocation", TransLocation.ToString());
// cmd.Parameters.AddWithValue("@Referencedocumentno", Referencedocumentno.ToString());
// cmd.Parameters.AddWithValue("@TransLineNo", k.ToString());
// cmd.ExecuteNonQuery();
// no += 1;
// //string Tquery = " Insert Into ax.AcxinventTrans " +
// // "([TransId],[SiteCode],[DATAAREAID],[RECID],[InventTransDate],[TransType],[DocumentType]," +
// // "[DocumentNo],[DocumentDate],[ProductCode],[TransQty],[TransUOM],[TransLocation],[Referencedocumentno],TransLineNo)" +
// // " Values ('" + TransId + "','" + Siteid + "','" + DATAAREAID + "',(select coalesce(max(Recid),0)+1 as Recid from ax.acxinventTrans),getdate()," + TransType + "," + DocumentType + ",'" + DocumentNo + "'," +
// // " '" + System.DateTime.Now + "','" + Product + "'," + TransQty + ",'BOX','" + TransLocation + "','" + Referencedocumentno + "'," + k + ")";
// // //obj.ExecuteCommand(query);
// // cmd.CommandText = Tquery;
// // cmd.ExecuteNonQuery();
// }
// else
// {
// dtForShownUnuploadData.Rows.Add();
// dtForShownUnuploadData.Rows[j]["Site Code"] = dtExcelData.Rows[k]["Site Code"].ToString();
// dtForShownUnuploadData.Rows[j]["Product Code"] = dtExcelData.Rows[k]["Product Code"].ToString();
// dtForShownUnuploadData.Rows[j]["Box Qty"] = dtExcelData.Rows[k]["Box Qty"].ToString();
// dtForShownUnuploadData.Rows[j]["Crate Qty"] = dtExcelData.Rows[k]["Crate Qty"].ToString();
// dtForShownUnuploadData.Rows[j]["Rejected Reason"] = "Qty always greater than zero..";
// j += 1;
// continue;
// }
// }
// tran.Commit();
// lblError.Text = "Records Uploaded Successfully. Total Records : " + dtExcelData.Rows.Count + ". Uploaded : " + no + " Records.";
// lblError.ForeColor = System.Drawing.Color.Green;
// if (dtForShownUnuploadData.Rows.Count > 0)
// {
// gridviewRecordNotExist.DataSource = dtForShownUnuploadData;
// gridviewRecordNotExist.DataBind();
// ModalPopupExtender1.Show();
// }
}
}
catch (Exception ex)
{
lblError.Text = ex.Message;
lblError.ForeColor = System.Drawing.Color.Red;
tran.Rollback();
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
if (conn != null)
{
if (conn.State.ToString() == "Open")
{
conn.Close();
}
}
}
}
private void SaveDetails()
{
try
{
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
//string Number="DT16000001";
#region getnTUMBER
string Number = string.Empty;
string query = "SELECT ISNULL(MAX(CAST(RIGHT(DISTRUCTION_NO,6) AS INT)),0)+1 FROM [ax].[ACXDISTRUCTIONNOTE] where SITEID='" + Session["SiteCode"].ToString() + "'";
conn = obj.GetConnection();
cmd1 = new SqlCommand(query);
transaction = conn.BeginTransaction();
cmd1.Connection = conn;
cmd1.Transaction = transaction;
cmd1.CommandTimeout = 3600;
cmd1.CommandType = CommandType.Text;
object vc = cmd1.ExecuteScalar();
Number = "DT" + System.DateTime.Today.ToString("yy") + ((int)vc).ToString("000000");
#endregion
#region insert into distruction table
if (Session["SecProductDetails"] != null)
{
//conn = obj.GetConnection();
cmd = new SqlCommand("ACX_INSERTDISTRUCTIONNOTE");
// transaction = conn.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = transaction;
cmd.CommandTimeout = 3600;
cmd.CommandType = CommandType.StoredProcedure;
int i = 0;
foreach (GridViewRow grv in gvDetails.Rows)
{
i = i + 1;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@SITEID", Session["SITECODE"].ToString());
if (rdoMarketReturnDistruction.Checked == true)
{
cmd.Parameters.AddWithValue("@CUSTOMERCODE", ddlCustomer.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@CUSTOMERCODE", "");
}
cmd.Parameters.AddWithValue("@DISTRUCTION_NO", Number);
cmd.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
cmd.Parameters.AddWithValue("@RECEIPT_NO", "");
cmd.Parameters.AddWithValue("@RECEIPTDATE", "");
cmd.Parameters.AddWithValue("@INVOICE_NO", "");
cmd.Parameters.AddWithValue("@INVOICEDATE", "");
cmd.Parameters.AddWithValue("@LINENO", i);
cmd.Parameters.AddWithValue("@PRODUCTCODE", grv.Cells[1].Text);
cmd.Parameters.AddWithValue("@BOX", grv.Cells[3].Text);
cmd.Parameters.AddWithValue("@BATCHNO", grv.Cells[5].Text.Replace(" ", ""));
cmd.Parameters.AddWithValue("@MFD", grv.Cells[6].Text);
cmd.Parameters.AddWithValue("@REMARK", grv.Cells[7].Text.Replace(" ", ""));
cmd.Parameters.AddWithValue("@PRICE", grv.Cells[8].Text);
cmd.Parameters.AddWithValue("@VALUE", Convert.ToDecimal(grv.Cells[9].Text));
if (rdoMarketReturnDistruction.Checked == true)
{
cmd.Parameters.AddWithValue("@DISTRUCTIONTYPE", 2);
}
else
{
cmd.Parameters.AddWithValue("@DISTRUCTIONTYPE", 1);
}
cmd.ExecuteNonQuery();
}
#endregion
#region insert into transaction table and claim table
cmd2 = new SqlCommand("ACX_DISTRUCTION_STOCK_MOVEMENT");
cmd2.Connection = conn;
cmd2.Transaction = transaction;
cmd2.CommandTimeout = 3600;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.Clear();
cmd2.Parameters.AddWithValue("@Siteid", Session["SITECODE"].ToString());
if (rdoColdRoomDistruction.Checked == true)
{
cmd2.Parameters.AddWithValue("@DISTRUCTIONTYPE", 1);
}
else
{
cmd2.Parameters.AddWithValue("@DISTRUCTIONTYPE", 2);
}
cmd2.Parameters.AddWithValue("@DISTRUCTION_NO", Number);
cmd2.ExecuteNonQuery();
#endregion
transaction.Commit();
ClearAll();
if (rdoMarketReturnDistruction.Checked == true)
{
rdMarketReturnDistruction_CheckedChanged(null, null);
}
string message = "alert('Distruction No : " + Number + " Generated Successfully.!');";
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", message, true);
}
}
catch (Exception ex)
{
transaction.Rollback();
ErrorSignal.FromCurrentContext().Raise(ex);
string message = "alert('Error:" + ex.Message + " !');";
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", message, true);
}
finally
{
if (conn != null)
{
if (conn.State.ToString() == "Open")
{
conn.Close();
conn.Dispose();
}
}
}
}
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 DataTable GetData(DateTime fromdate)
{
DataTable dt = null;
try
{
SqlConnection con = baseObj.GetConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = string.Empty;
cmd.CommandType = CommandType.StoredProcedure;
if (drptargetType.SelectedValue.ToString() == "1")
{
cmd.CommandText = "ACX_TARGET_DISTRIBUTOREXPENSE";
}
if (drptargetType.SelectedValue.ToString() == "2")
{
cmd.CommandText = "ACX_TARGET_ANNUALEXPENSE";
}
if (drptargetType.SelectedValue.ToString() == "3")
{
cmd.CommandText = "ACX_GETPSRSTIPEND";
}
if (drptargetType.SelectedValue.ToString() == "4")
{
cmd.CommandText = "ACX_TARGET_VRSEXPENSE";
}
if (drptargetType.SelectedValue.ToString() == "5")
{
cmd.CommandText = "ACX_TARGET_VRSEXPENSE1";
}
if (drptargetType.SelectedValue.ToString() == "6")
//{ cmd.CommandText = "ACX_TARGET_VRSCLAIMSETUP"; }
{
cmd.CommandText = "ACX_TARGET_VRSCLAIMSETUP_MONTHLY";
}
if (drptargetType.SelectedValue.ToString() == "7")
{
cmd.CommandText = "ACX_TARGET_VDEXPENSE2";
}
if (drptargetType.SelectedValue.ToString() == "8")
{
cmd.CommandText = "ACX_TARGET_VDEXPENSE3";
}
if (drptargetType.SelectedValue.ToString() == "9")
{
cmd.CommandText = "ACX_TARGET_VDEXPENSE4";
}
if (drptargetType.SelectedValue.ToString() == "10")
{
cmd.CommandText = "ACX_TARGET_VDEXPENSE5";
}
cmd.Parameters.Add("@EXPENSEMONTH", SqlDbType.SmallDateTime).Value = fromdate.ToString("yyyy-MM-01");
cmd.Parameters.Add("@SiteId", SqlDbType.NVarChar).Value = Convert.ToString(Session["SiteCode"]);
cmd.Parameters.Add("@BUCode", SqlDbType.NVarChar).Value = ddlBusinessUnit.SelectedItem.Value;
// cmd.Parameters.Add("@VRSCODE", SqlDbType.NVarChar).Value = Convert.ToString(Session["SiteCode"]);
SqlDataAdapter ad = new SqlDataAdapter(cmd);
dt = new DataTable();
ad.Fill(dt);
return(dt);
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
return(dt);
}
}