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