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