public bool IsSupplierInvNoExist(GRN grn) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); DbCommand cmd = db.GetStoredProcCommand(Constant.SP_GRN_Check_SupInvNo_Exist); db.AddInParameter(cmd, "@sSuplierInvNo", DbType.String, grn.SuplierInvNo); db.AddOutParameter(cmd, "@iResult", DbType.Int16, 4); db.ExecuteReader(cmd); int output = Int32.Parse(db.GetParameterValue(cmd, "@iResult").ToString()); if (output > 0) { result = true; } } catch (System.Exception ex) { ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool IsSupplierInvNoExist(GRN grn)"); throw ex; } return(result); }
private bool UpdateGRNItems(GRN grn, Database db, DbTransaction transaction) { bool result = false; try { DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_GRN_InsertDetails); db.AddInParameter(insCommand, "@iId", DbType.Int64, "Id", DataRowVersion.Current); db.AddInParameter(insCommand, "@biGRNId", DbType.Int64, grn.GRNId); db.AddInParameter(insCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current); db.AddInParameter(insCommand, "@iReceivedQty", DbType.Int32, "ReceivedQty", DataRowVersion.Current); db.AddInParameter(insCommand, "@mItemValue", DbType.Currency, "ItemValue", DataRowVersion.Current); DbCommand delCommand = db.GetStoredProcCommand(Constant.SP_GRN_DeleteDetails); //db.AddInParameter(insCommand, "@iId", DbType.Int64, "Id");not yet done db.AddInParameter(delCommand, "@biGRNId", DbType.Int64, grn.GRNId); db.AddInParameter(delCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current); db.UpdateDataSet(grn.GRNItems, grn.GRNItems.Tables[0].TableName, insCommand, null, delCommand, transaction); result = true; } catch (System.Exception ex) { throw ex; } return(result); }
public DataSet GetGRNDetailsByGRNID(GRN grn) { try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); DbCommand cmd = db.GetStoredProcCommand(Constant.SP_GRN_Get_Details_By_ID); db.AddInParameter(cmd, "@biGRNId", DbType.Int64, grn.GRNId); return(db.ExecuteDataSet(cmd)); } catch (System.Exception ex) { ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool GetGRNByID(GRN grn)"); throw ex; return(null); } }
public bool GetGRNByIDAndCustId(GRN grn, Int32 customerId) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); DbCommand cmd = db.GetStoredProcCommand(Constant.SP_GRN_Get_By_IDAndCustId); db.AddInParameter(cmd, "@biGRNId", DbType.Int64, grn.GRNId); db.AddInParameter(cmd, "@iCustId", DbType.Int32, customerId); IDataReader reader = db.ExecuteReader(cmd); if (reader != null) { while (reader.Read()) { grn.GRNId = reader["GRNId"] != DBNull.Value ? Convert.ToInt64(reader["GRNId"].ToString()) : 0; //if (reader["POId"] == DBNull.Value || reader["POId"].ToString() == String.Empty) //{ // grn.POId = null; //} //else //{ // grn.POId = Convert.ToInt32(reader["POId"].ToString()); //} //if (reader["SalesReturnID"] == DBNull.Value || reader["SalesReturnID"].ToString() == String.Empty) //{ // grn.SalesReturnID = null; //} //else //{ // grn.SalesReturnID = reader["SalesReturnID"] != DBNull.Value ? Convert.ToInt32(reader["SalesReturnID"].ToString()) : 0; //} //if (reader["InvoiceId"] == DBNull.Value || reader["InvoiceId"].ToString() == String.Empty) //{ // grn.InvId = null; //} //else //{ // grn.InvId = reader["InvoiceId"] != DBNull.Value ? Convert.ToInt32(reader["InvoiceId"].ToString()) : 0; // grn.GRNInvoice.InvoiceId = grn.InvId.Value; // grn.GRNInvoice.GetInvoiceByInvoiceID(); //} grn.Rec_Date = reader["Rec_Date"] != DBNull.Value ? Convert.ToDateTime(reader["Rec_Date"].ToString()) : DateTime.MinValue; grn.Rec_By = reader["Rec_By"] != DBNull.Value ? Convert.ToInt32(reader["Rec_By"].ToString()) : 0; grn.TotalAmount = reader["ReceivedTotal"] != DBNull.Value ? Convert.ToDecimal(reader["ReceivedTotal"].ToString()) : 0; //grn.SuplierInvNo = reader["SuplierInvNo"].ToString(); grn.TotalPaid = reader["SupplierPaidAmmount"] != DBNull.Value ? Convert.ToDecimal(reader["SupplierPaidAmmount"].ToString()) : 0; //grn.GRNItems = this.GetGRNDetailsByGRNID(grn); result = true; } } } catch (System.Exception ex) { ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool GetGRNByID(GRN grn)"); throw ex; } return(result); }
public bool AddGRN(GRN grn) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_GRN_Insert); if (grn.POId.HasValue) { db.AddInParameter(dbCommand, "@iPOId", DbType.Int32, grn.POId); } else { db.AddInParameter(dbCommand, "@iPOId", DbType.Int32, DBNull.Value); } if (grn.InvId.HasValue) { db.AddInParameter(dbCommand, "@iInvId", DbType.Int32, grn.InvId); } else { db.AddInParameter(dbCommand, "@iInvId", DbType.Int32, DBNull.Value); } if (grn.SalesReturnID.HasValue) { db.AddInParameter(dbCommand, "@iSalesReturnID", DbType.Int32, grn.SalesReturnID); } else { db.AddInParameter(dbCommand, "@iSalesReturnID", DbType.Int32, DBNull.Value); } db.AddInParameter(dbCommand, "@iRec_By", DbType.Int32, grn.Rec_By); db.AddInParameter(dbCommand, "@sSuplierInvNo", DbType.String, grn.SuplierInvNo); db.AddInParameter(dbCommand, "@mReceivedTotal", DbType.Decimal, grn.TotalAmount); db.AddInParameter(dbCommand, "@sCreditNote", DbType.String, grn.CreditNote); db.AddInParameter(dbCommand, "@dtRec_Date", DbType.DateTime, grn.Rec_Date); db.AddOutParameter(dbCommand, "@iGRNId", DbType.Int64, 4); if (db.ExecuteNonQuery(dbCommand, transaction) > 0) { Int64 newGRNID = Convert.ToInt64(db.GetParameterValue(dbCommand, "@iGRNId")); if (newGRNID > 0) { grn.GRNId = newGRNID; if (this.UpdateGRNItems(grn, db, transaction)) { transaction.Commit(); result = true; } } } } catch (System.Exception ex) { transaction.Rollback(); ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool AddGRN(GRN grn)"); throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return(result); }
private void SetData() { try { if (hdnFromURL.Value.Trim() != "ProductSearch.aspx" && hdnFromURL.Value.Trim() != "SearchGroupItems.aspx") { Master.ClearSessions();//Clear all sessions ObjInv.BranchId = Master.LoggedUser.BranchID; txtInvNo.Text = new InvoiceDAO().GetNextCodeForInvoice(ObjInv); } // // Single Item from Search // if (hdnItemIdFromSearch.Value.Trim() != "0") { Int32 itemIdFS = Int32.Parse(hdnItemIdFromSearch.Value.Trim()); Item objItem = new Item(); objItem.ItemId = itemIdFS; objItem.GetItemByID(); rblItemType.SelectedValue = "1"; txtItemCode.Text = objItem.ItemCode.ToString(); txtItemName.Text = objItem.ItemDescription.Trim(); txtItemSelPrice.Text = Decimal.Round(objItem.SellingPrice, 2).ToString(); hdnPriceBeforeDiscount.Value = txtItemSelPrice.Text.Trim(); txtMinSellingPrice.Value = Decimal.Round(objItem.Cost, 2).ToString(); txtAvailableQty.Value = (objItem.QuantityInHand - objItem.InvoicedQty).ToString(); ddlCreditOption.SelectedValue = ObjInv.CreditOption.ToString(); CreditOptionSelectChange(); } // // Group Item from Search // if (hdnGroupIdFromSearch.Value.Trim() != "0") { Int32 itemIdFS = Int32.Parse(hdnGroupIdFromSearch.Value.Trim()); Group objGItem = new Group(); objGItem.GroupId = short.Parse(itemIdFS.ToString()); objGItem.GetGroupByID(); rblItemType.SelectedValue = "2"; txtItemCode.Text = objGItem.GroupCode.Trim(); txtItemName.Text = objGItem.GroupName.Trim(); txtItemSelPrice.Text = Decimal.Round(objGItem.SellingPrice, 2).ToString(); txtMinSellingPrice.Value = Decimal.Round(objGItem.Cost, 2).ToString(); txtAvailableQty.Value = objGItem.QuantityInHand.ToString(); ddlCreditOption.SelectedValue = ObjInv.CreditOption.ToString(); CreditOptionSelectChange(); } if (ObjInv.InvoiceId > 0 || hdnItemIdFromSearch.Value.Trim() != "0" || hdnGroupIdFromSearch.Value.Trim() != "0") { if (ObjInv.IsIssued || ObjInv.Status == Structures.InvoiceStatus.Cancelled) { this.SetInvoiceReadOnly(); } if (ObjInv.IsVoucherPaymentMade) { btnCheckGRNId.Enabled = false; txtPaidAmount.Enabled = false; ddlPaymentType.Enabled = false; trCreditOption.Visible = false; trCreditOptionBel.Visible = false; } txtInvNo.Text = ObjInv.InvoiceNo; if (ObjInv.GRNId.HasValue) { txtGRNId.Text = ObjInv.GRNId.ToString(); hdnGRNId.Value = ObjInv.GRNId.ToString(); txtCustomerCredBal.Text = ObjInv.CustDebitTotal.ToString(); } /// /// Recall grn details for the validator if Invoice id is zero /// if (ObjInv.InvoiceId == 0) { if (txtGRNId.Text.Trim() != string.Empty) { GRN receiveGoods = new GRN(); receiveGoods.GRNId = ObjInv.GRNId.Value; Int32 custId = ObjInv.CustomerID.HasValue ? ObjInv.CustomerID.Value : 1; if (new GRNDAO().GetGRNByIDAndCustId(receiveGoods, custId)) { rvCustomerBal.MaximumValue = decimal.Round((receiveGoods.TotalAmount - receiveGoods.TotalPaid), 2).ToString(); } } } if (ObjInv.I_in.HasValue && ObjInv.I_in == true) { chkIsHidden.Checked = true; } else { chkIsHidden.Checked = false; } //lblDate.Text = ObjInv.CreatedDate.ToShortDateString(); dpDate.Date = ObjInv.CreatedDate; ddlPaymentType.SelectedValue = ObjInv.PaymentType; ddlCustomerCode.SelectedValue = ObjInv.CustomerID.HasValue ? ObjInv.CustomerID.ToString() : "1"; ddlCardType.SelectedValue = Convert.ToInt16(ObjInv.CardType).ToString(); //Fill customer data this.CustomerSelectChange(); if (ObjInv.InvoiceId > 0) { ddlInvoicedBy.SelectedValue = ObjInv.CreatedUser.ToString(); btnCheckGRNId.Enabled = false; txtGRNId.Enabled = false; rvCustomerBal.Enabled = false; trCreditOption.Visible = false; trCreditOptionBel.Visible = false; dpDate.Enabled = false; } else { ddlInvoicedBy.SelectedValue = Master.LoggedUser.UserId.ToString(); } if (ObjInv.DsInvoiceDetails != null) { gvItemList.DataSource = ObjInv.DsInvoiceDetails; gvItemList.DataBind(); } if (Decimal.Round((ObjInv.GrandTotal - ObjInv.DueAmount), 2) == 0) { txtPaidAmount.Text = String.Empty; } else { txtPaidAmount.Text = Decimal.Round((ObjInv.GrandTotal - ObjInv.DueAmount), 2).ToString(); } lblBalancePayment.Text = Decimal.Round(ObjInv.DueAmount, 2).ToString(); lblInvAmount.Text = ObjInv.GrandTotal.ToString(); txtLineItemsCount.Value = gvItemList.Rows.Count.ToString(); txtChequeNumber.Text = ObjInv.ChequeNumber.ToString(); if (ObjInv.ChequeDate != DateTime.MinValue) { dxdcChequeDate.Value = ObjInv.ChequeDate; } if (ObjInv.InvoiceId > 0) { tdPrintInvoice.Visible = true; trRemarks.Visible = true; } else { tdPrintInvoice.Visible = false; trRemarks.Visible = false; } txtCancelNote.Value = ObjInv.Remarks.Trim(); } if (ObjInv.InvoiceId == 0) { ddlInvoicedBy.SelectedValue = Master.LoggedUser.UserId.ToString(); } this.ShowHideGrid(); } catch (Exception ex) { throw ex; } }
protected void btnCheckGRNId_Click(object sender, EventArgs e) { try { //if (hdnGRNId.Value == "0") //{ if (txtGRNId.Text.Trim() != string.Empty) { lblGRNError.Visible = false; GRN receiveGoods = new GRN(); receiveGoods.GRNId = Int64.Parse(txtGRNId.Text.Trim()); Int32 custId = 0; custId = Int32.Parse(ddlCustomerCode.SelectedValue.Trim()); if(new GRNDAO().GetGRNByIDAndCustId(receiveGoods,custId)) { txtCustomerCredBal.Text = decimal.Round((receiveGoods.TotalAmount - receiveGoods.TotalPaid),2).ToString(); hdnGRNId.Value = receiveGoods.GRNId.ToString(); ObjInv.GRNId = receiveGoods.GRNId; txtPaidAmount.Text = decimal.Round((receiveGoods.TotalAmount - receiveGoods.TotalPaid), 2).ToString(); ddlCreditOption.SelectedValue = "2";//GRN ddlCreditOption.Enabled = false; rvCustomerBal.Enabled = true; rvCustomerBal.MaximumValue = decimal.Round((receiveGoods.TotalAmount - receiveGoods.TotalPaid), 2).ToString(); } else { lblGRNError.Text = "Incorrect GRNId"; lblGRNError.Visible = true; rvCustomerBal.Enabled = false; ddlCreditOption.Enabled = true; } } //} } catch (Exception ex) { ex.Data.Add("UILayerException", this.GetType().ToString() + Constant.Error_Seperator + "protected void btnCheckGRNId_Click(object sender, EventArgs e)"); if (Master.LoggedUser != null && Master.LoggedUser.UserName != null && Master.LoggedUser.UserName != string.Empty) Response.Redirect("Error.aspx?LogId=" + LankaTilesExceptions.WriteEventLogs(ex, Constant.Database_Connection_Name, Master.LoggedUser.UserName), false); else Response.Redirect("Error.aspx?LogId=" + LankaTilesExceptions.WriteEventLogs(ex, Constant.Database_Connection_Name, "Annonimous"), false); } }
private bool UpdateGRNItems(GRN grn, Database db, DbTransaction transaction) { bool result = false; try { DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_GRN_InsertDetails); db.AddInParameter(insCommand, "@iId", DbType.Int64, "Id",DataRowVersion.Current); db.AddInParameter(insCommand, "@biGRNId", DbType.Int64, grn.GRNId); db.AddInParameter(insCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current); db.AddInParameter(insCommand, "@iReceivedQty", DbType.Int32, "ReceivedQty", DataRowVersion.Current); db.AddInParameter(insCommand, "@mItemValue", DbType.Currency, "ItemValue", DataRowVersion.Current); DbCommand delCommand = db.GetStoredProcCommand(Constant.SP_GRN_DeleteDetails); //db.AddInParameter(insCommand, "@iId", DbType.Int64, "Id");not yet done db.AddInParameter(delCommand, "@biGRNId", DbType.Int64, grn.GRNId); db.AddInParameter(delCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current); db.UpdateDataSet(grn.GRNItems, grn.GRNItems.Tables[0].TableName, insCommand, null, delCommand, transaction); result = true; } catch (System.Exception ex) { throw ex; } return result; }
//this is the update public bool UpdateGRN(GRN grn) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_GRN_Update); db.AddInParameter(dbCommand, "@iGRNId", DbType.Int64, grn.GRNId); if (grn.POId.HasValue) { db.AddInParameter(dbCommand, "@iPOId", DbType.Int32, grn.POId); } else { db.AddInParameter(dbCommand, "@iPOId", DbType.Int32, DBNull.Value); } if (grn.SalesReturnID.HasValue) { db.AddInParameter(dbCommand, "@iSalesReturnID", DbType.Int32, grn.SalesReturnID); } else { db.AddInParameter(dbCommand, "@iSalesReturnID", DbType.Int32, DBNull.Value); } db.AddInParameter(dbCommand, "@iRec_By", DbType.Int32, grn.Rec_By); db.AddInParameter(dbCommand, "@sSuplierInvNo", DbType.String, grn.SuplierInvNo); if (db.ExecuteNonQuery(dbCommand, transaction) > 0) { if (this.UpdateGRNItems(grn, db, transaction)) { transaction.Commit(); result = true; } } } catch (System.Exception ex) { transaction.Rollback(); ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool AddGRN(GRN grn)"); throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return result; }
public bool IsSupplierInvNoExist(GRN grn) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); DbCommand cmd = db.GetStoredProcCommand(Constant.SP_GRN_Check_SupInvNo_Exist); db.AddInParameter(cmd, "@sSuplierInvNo", DbType.String, grn.SuplierInvNo); db.AddOutParameter(cmd, "@iResult", DbType.Int16, 4); db.ExecuteReader(cmd); int output = Int32.Parse(db.GetParameterValue(cmd, "@iResult").ToString()); if (output > 0) { result = true; } } catch (System.Exception ex) { ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool IsSupplierInvNoExist(GRN grn)"); throw ex; } return result; }
public DataSet GetGRNDetailsByGRNID(GRN grn) { try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); DbCommand cmd = db.GetStoredProcCommand(Constant.SP_GRN_Get_Details_By_ID); db.AddInParameter(cmd, "@biGRNId", DbType.Int64, grn.GRNId); return db.ExecuteDataSet(cmd); } catch (System.Exception ex) { ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool GetGRNByID(GRN grn)"); throw ex; return null; } }
public bool GetGRNByIDAndCustId(GRN grn,Int32 customerId) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); DbCommand cmd = db.GetStoredProcCommand(Constant.SP_GRN_Get_By_IDAndCustId); db.AddInParameter(cmd, "@biGRNId", DbType.Int64, grn.GRNId); db.AddInParameter(cmd, "@iCustId", DbType.Int32, customerId); IDataReader reader = db.ExecuteReader(cmd); if (reader != null) { while (reader.Read()) { grn.GRNId = reader["GRNId"] != DBNull.Value ? Convert.ToInt64(reader["GRNId"].ToString()) : 0; //if (reader["POId"] == DBNull.Value || reader["POId"].ToString() == String.Empty) //{ // grn.POId = null; //} //else //{ // grn.POId = Convert.ToInt32(reader["POId"].ToString()); //} //if (reader["SalesReturnID"] == DBNull.Value || reader["SalesReturnID"].ToString() == String.Empty) //{ // grn.SalesReturnID = null; //} //else //{ // grn.SalesReturnID = reader["SalesReturnID"] != DBNull.Value ? Convert.ToInt32(reader["SalesReturnID"].ToString()) : 0; //} //if (reader["InvoiceId"] == DBNull.Value || reader["InvoiceId"].ToString() == String.Empty) //{ // grn.InvId = null; //} //else //{ // grn.InvId = reader["InvoiceId"] != DBNull.Value ? Convert.ToInt32(reader["InvoiceId"].ToString()) : 0; // grn.GRNInvoice.InvoiceId = grn.InvId.Value; // grn.GRNInvoice.GetInvoiceByInvoiceID(); //} grn.Rec_Date = reader["Rec_Date"] != DBNull.Value ? Convert.ToDateTime(reader["Rec_Date"].ToString()) : DateTime.MinValue; grn.Rec_By = reader["Rec_By"] != DBNull.Value ? Convert.ToInt32(reader["Rec_By"].ToString()) : 0; grn.TotalAmount = reader["ReceivedTotal"] != DBNull.Value ? Convert.ToDecimal(reader["ReceivedTotal"].ToString()) : 0; //grn.SuplierInvNo = reader["SuplierInvNo"].ToString(); grn.TotalPaid = reader["SupplierPaidAmmount"] != DBNull.Value ? Convert.ToDecimal(reader["SupplierPaidAmmount"].ToString()) : 0; //grn.GRNItems = this.GetGRNDetailsByGRNID(grn); result = true; } } } catch (System.Exception ex) { ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool GetGRNByID(GRN grn)"); throw ex; } return result; }