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); } }