private bool SavePurchaseReturnDetail(mdlPurchaseReturn objmdlPurchaseReturn, SqlTransaction transaction, string SaveUpdate, string PurchaseOrDelivery, SqlCommand cmd)
{
if (SaveUpdate == "Update")
{
if (PurchaseOrDelivery == "P")
{
if (objmdlPurchaseReturn.DetailPurchaseReturn.Rows.Count > 0)
{
cmd = new SqlCommand("Delete From D_PurchaseReturn WHERE [ReturnId] = '" + objmdlPurchaseReturn.pk_ReturnId + "'", con, transaction);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
if (PurchaseOrDelivery == "D")
{
cmd = new SqlCommand("Delete From D_DeliveryChallan WHERE [Fk_DekiveryChallan] = '" + objmdlPurchaseReturn.pk_ReturnId + "'", con, transaction);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
bool variable = false;
try
{
if (objmdlPurchaseReturn.DetailPurchaseReturn.Rows.Count > 0)
{
cmd = new SqlCommand("stp_ERP_PurchaseReturn_CRUD", con, transaction);
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i <= objmdlPurchaseReturn.DetailPurchaseReturn.Rows.Count - 1; i++)
{
cmd.Parameters.Clear();
if (Convert.ToDouble(objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Qty"].ToString()) > 0)
{
cmd.Parameters.Add("@pk_ReturnId", SqlDbType.VarChar, 9).Value = objmdlPurchaseReturn.pk_ReturnId;
cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 6).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["ID"].ToString().Replace("-", "");
cmd.Parameters.Add("@StockType", SqlDbType.VarChar, 50).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Type"].ToString().Replace("-", "");
cmd.Parameters.Add("@BatchNo", SqlDbType.VarChar, 15).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Batch No"].ToString();
cmd.Parameters.Add("@ExpiryDate", SqlDbType.DateTime).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Exp. Date"].ToString();
cmd.Parameters.Add("@ManfDate", SqlDbType.DateTime).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Mfg. Date"].ToString();
cmd.Parameters.Add("@TP", SqlDbType.Decimal).Value = Convert.ToDecimal(objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["FlatRate"]);
cmd.Parameters.Add("@Quantity", SqlDbType.Decimal).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Qty"];
cmd.Parameters.Add("@DiscountPer", SqlDbType.Decimal).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Disc %"];
cmd.Parameters.Add("@DiscountDetail", SqlDbType.Decimal).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Discount"];
cmd.Parameters.Add("@STaxValue", SqlDbType.Decimal).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["STaxValue"];
cmd.Parameters.Add("@SaleTaxPer", SqlDbType.Decimal).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["SaleTaxPer"];
cmd.Parameters.Add("@SaleTaxDetail", SqlDbType.Decimal).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Sale Tax"];
cmd.Parameters.Add("@Net", SqlDbType.Decimal).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Net"];
cmd.Parameters.Add("@GrossValue", SqlDbType.Decimal).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["GrossValue"];
cmd.Parameters.Add("@CostRate", SqlDbType.Decimal).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["CostRate"];
cmd.Parameters.Add("@FlateRate", SqlDbType.Decimal).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["TP"];
cmd.Parameters.Add("@AvgRate", SqlDbType.Decimal).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["AvgRate"];
if (SaveUpdate == "Update")
{
if (PurchaseOrDelivery == "P")
{
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 20).Value = "SaveDetail";
}
if (PurchaseOrDelivery == "D")
{
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 20).Value = "SaveDetailD";
}
}
if (SaveUpdate == "Save")
{
if (PurchaseOrDelivery == "P")
{
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 20).Value = "SaveDetail";
}
if (PurchaseOrDelivery == "D")
{
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 20).Value = "SaveDetailD";
}
}
cmd.ExecuteNonQuery();
variable = true;
cmd.Parameters.Clear();
}
else
{
variable = false;
}
}
}
}
catch (Exception ex)
{
try
{
}
catch (Exception e)
{
variable = false;
MessageBox.Show(e.Message, "ERP - Babar Medicine Company Lahore", MessageBoxButtons.OK, MessageBoxIcon.Error);
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
variable = false;
if (con.State == ConnectionState.Open)
{
con.Close();
}
MessageBox.Show(ex.Message, "ERP - Babar Medicine Company Lahore", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return variable;
}
//-------------------------------------------POST--------------------------------------------------//
//Post Purchase Return
public bool PostPurchaseReturn(mdlPurchaseReturn objmdlPurchaseReturn, string SaveUpdate)
{
bool ISPost = false;
if (con.State == ConnectionState.Open)
{
con.Close();
}
con.Open();
SqlTransaction tra = con.BeginTransaction();
SqlCommand cmdd = new SqlCommand("stp_ERP_PurchaseReturn_Post", con, tra);
cmdd.CommandType = CommandType.StoredProcedure;
try
{
cmdd = (PostPR(objmdlPurchaseReturn, tra, cmdd));
ISPost = true;
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
catch (Exception ex)
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
MessageBox.Show(ex.Message, "ERP - Babar Medicine Company Lahore", MessageBoxButtons.OK, MessageBoxIcon.Error);
ISPost = false;
}
return ISPost;
}
public bool SaveUpdateDC(mdlPurchaseReturn objmdlPurchaseReturn, string SaveUpdate)
{
bool IsSaved = false;
if (con.State == ConnectionState.Open)
{
con.Close();
}
con.Open();
SqlTransaction transaction = con.BeginTransaction();
SqlCommand cmd = new SqlCommand("stp_ERP_PurchaseReturn_CRUD", con, transaction);
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@pk_ReturnId", SqlDbType.VarChar, 9).Value = objmdlPurchaseReturn.pk_ReturnId;
cmd.Parameters.Add("@ReturnDate", SqlDbType.DateTime).Value = objmdlPurchaseReturn.ReturnDate;
cmd.Parameters.Add("@SupplierId", SqlDbType.VarChar, 3).Value = objmdlPurchaseReturn.SupplierId;
cmd.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = objmdlPurchaseReturn.DueDate;
cmd.Parameters.Add("@RefNo", SqlDbType.VarChar, 50).Value = objmdlPurchaseReturn.RefNo;
cmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = objmdlPurchaseReturn.CustomerId;
cmd.Parameters.Add("@Narration", SqlDbType.VarChar, 50).Value = objmdlPurchaseReturn.Narration;
cmd.Parameters.Add("@NetAmount", SqlDbType.Decimal).Value = objmdlPurchaseReturn.NetAmount;
cmd.Parameters.Add("@SaleTax", SqlDbType.Decimal).Value = objmdlPurchaseReturn.SaleTax;
cmd.Parameters.Add("@Discount", SqlDbType.Decimal).Value = objmdlPurchaseReturn.Discount;
cmd.Parameters.Add("@GrossAmount", SqlDbType.Decimal).Value = objmdlPurchaseReturn.GrossAmount;
cmd.Parameters.Add("@AddUser", SqlDbType.VarChar, 16).Value = objmdlPurchaseReturn.AddUser;
cmd.Parameters.Add("@Remarks", SqlDbType.VarChar, 500).Value = objmdlPurchaseReturn.Remarks;
if (SaveUpdate == "Save")
{
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 20).Value = "SaveMasterDC";
}
if (SaveUpdate == "Update")
{
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 20).Value = "UpdateMasterDC";
}
if (SaveUpdate == "PostDC")
{
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 20).Value = "Post";
}
cmd.ExecuteNonQuery();
if (SavePurchaseReturnDetail(objmdlPurchaseReturn, transaction, SaveUpdate, "D", cmd)) { transaction.Commit(); IsSaved = true; }
else { transaction.Rollback(); IsSaved = false; }
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
catch (Exception ex)
{
transaction.Rollback();
if (con.State == ConnectionState.Open)
{
con.Close();
}
MessageBox.Show(ex.Message, "ERP - Babar Medicine Company Lahore", MessageBoxButtons.OK, MessageBoxIcon.Error);
IsSaved = false;
}
return IsSaved;
}
public SqlCommand PostPR(mdlPurchaseReturn objmdlPurchaseReturn, SqlTransaction Trasaction, SqlCommand cmd)
{
string Quantity = "";
bool RollBack = false;
try
{
if (objmdlPurchaseReturn.DetailPurchaseReturn.Rows.Count > 0)
{
cmd = new SqlCommand("stp_ERP_PurchaseReturn_Post", con, Trasaction);
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i <= objmdlPurchaseReturn.DetailPurchaseReturn.Rows.Count - 1; i++)
{
cmd.Parameters.Clear();
if (Convert.ToDouble(objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Qty"].ToString()) > 0)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 6).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["ID"].ToString();
cmd.Parameters.Add("@StockType", SqlDbType.VarChar, 50).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Type"].ToString();
cmd.Parameters.Add("@BatchNo", SqlDbType.VarChar, 15).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Batch No"].ToString();
cmd.Parameters.Add("@ExpiryDate", SqlDbType.VarChar, 20).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Exp. Date"].ToString();
cmd.Parameters.Add("@ManfactureDate", SqlDbType.VarChar, 20).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Mfg. Date"].ToString();
cmd.Parameters.Add("@CostRate", SqlDbType.Decimal).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["CostRate"];
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 20).Value = "Verify";
cmd.ExecuteNonQuery();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
Quantity = (reader.GetValue(0)).ToString();
}
reader.Close();
if (Quantity != "")
{
if (Convert.ToInt32(Quantity) > Convert.ToInt32(objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Qty"]))
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 6).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["ID"].ToString();
cmd.Parameters.Add("@StockType", SqlDbType.VarChar, 50).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Type"].ToString();
cmd.Parameters.Add("@BatchNo", SqlDbType.VarChar, 15).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Batch No"].ToString();
cmd.Parameters.Add("@ExpiryDate", SqlDbType.VarChar, 20).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Exp. Date"].ToString();
cmd.Parameters.Add("@ManfactureDate", SqlDbType.VarChar, 20).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Mfg. Date"].ToString();
cmd.Parameters.Add("@CostRate", SqlDbType.Decimal).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["CostRate"];
cmd.Parameters.Add("@Quantity", SqlDbType.Decimal).Value = objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Qty"];
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 20).Value = "UpdateQty";
cmd.ExecuteNonQuery();
//---------------------Update Status M_Purchase REturn -------------------------//
cmd.Parameters.Clear();
cmd.Parameters.Add("@pk_ReturnId", SqlDbType.VarChar, 9).Value = objmdlPurchaseReturn.pk_ReturnId;
cmd.Parameters.Add("@AddUser", SqlDbType.VarChar, 16).Value = objmdlPurchaseReturn.AddUser;
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 20).Value = "UpdateStatusPR";
cmd.ExecuteNonQuery();
}
else
{
MessageBox.Show(objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["ID"].ToString() + " - " + objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Batch No"].ToString() + "- " + objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["CostRate"].ToString() + "Does not have enough quantity", "Babar Medicine Company Lahore - ERP", MessageBoxButtons.OK, MessageBoxIcon.Warning);
Trasaction.Rollback();
RollBack = true;
break;
}
}
else
{
MessageBox.Show(objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["ID"].ToString() + " - " + objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["Batch No"].ToString() + " - " + objmdlPurchaseReturn.DetailPurchaseReturn.Rows[i]["CostRate"].ToString() + " Not Found", "Babar Medicine Company Lahore - ERP", MessageBoxButtons.OK, MessageBoxIcon.Warning);
Trasaction.Rollback();
RollBack = true;
break;
}
}
}
if (!RollBack)
{
Trasaction.Commit();
}
}
}
catch (Exception ex)
{
Trasaction.Rollback();
if (con.State == ConnectionState.Open)
{
con.Close();
}
MessageBox.Show(ex.Message, "ERP - Babar Medicine Company Lahore", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return cmd;
}