public bool SavePurchaseOrder(mdlPurchaseOrder obj_mdlPurchaseOrder, string Operation)
{
bool IsSaved = false;
if (con.State == ConnectionState.Open)
{
con.Close();
}
con.Open();
SqlTransaction transaction = con.BeginTransaction();
SqlCommand cmd = new SqlCommand("stp_ERP_PurchaseOrder_Master", con, transaction);
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Pk_OrderID", SqlDbType.VarChar, 9).Value = obj_mdlPurchaseOrder.PK_PurchaseOrder;
cmd.Parameters.Add("@OrderDate", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseOrder.OrderDate;
cmd.Parameters.Add("@CompanyRefNo", SqlDbType.VarChar, 15).Value = obj_mdlPurchaseOrder.CompanyrefNo;
cmd.Parameters.Add("@OurRef", SqlDbType.VarChar, 15).Value = obj_mdlPurchaseOrder.OurRef;
cmd.Parameters.Add("@CompOrderType", SqlDbType.VarChar, 50).Value = obj_mdlPurchaseOrder.CompOrderType;
cmd.Parameters.Add("@CompRefDate", SqlDbType.VarChar, 12).Value = obj_mdlPurchaseOrder.CompRefDate;
cmd.Parameters.Add("@SupplierID", SqlDbType.VarChar, 3).Value = obj_mdlPurchaseOrder.SupplierID;
cmd.Parameters.Add("@DueDate", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseOrder.DueDate;
//cmd.Parameters.Add("@Status", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseOrder.Status;
cmd.Parameters.Add("@GrossAmount", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.GrossAmount;
cmd.Parameters.Add("@Discount", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.Discount;
cmd.Parameters.Add("@Saletax", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.SaleTax;
cmd.Parameters.Add("@NetAmnount", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.NetAmount;
cmd.Parameters.Add("@AddUser", SqlDbType.VarChar, 16).Value = obj_mdlPurchaseOrder.AddUser;
cmd.Parameters.Add("@PostUser", SqlDbType.VarChar, 16).Value = obj_mdlPurchaseOrder.Postuser;
cmd.Parameters.Add("@PostDate", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseOrder.PostDate;
cmd.Parameters.Add("@PostTime", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseOrder.PostTime;
cmd.Parameters.Add("@PaymentRefNo", SqlDbType.VarChar, 15).Value = obj_mdlPurchaseOrder.PaymentRefNo;
cmd.Parameters.Add("@PaymentRefDate", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseOrder.PaymentRefDate;
if (Operation == "Save")
{
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 12).Value = "Save";
}
else if (Operation == "Update")
{
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 12).Value = "Update";
}
cmd.ExecuteNonQuery();
//Inserting Purchase Order Details
cmd = SaveOrderDetails(obj_mdlPurchaseOrder, transaction, cmd);
transaction.Commit();
IsSaved = true;
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;
}
private SqlCommand SaveOrderDetails(mdlPurchaseOrder obj_mdlPurchaseOrder, SqlTransaction transaction, SqlCommand cmd)
{
//Deleting Order Details
if (obj_mdlPurchaseOrder.dtPurchaseOrder.Rows.Count > 0)
{
cmd = new SqlCommand("Delete from D_PurchaseOrder where OrderId ='" + obj_mdlPurchaseOrder.PK_PurchaseOrder + "'", con, transaction);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
//Inserting Order Details
if (obj_mdlPurchaseOrder.dtPurchaseOrder.Rows.Count > 0)
{
cmd = new SqlCommand("stp_ERP_PurchaseOrder_Detail", con, transaction);
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i <= obj_mdlPurchaseOrder.dtPurchaseOrder.Rows.Count - 1; i++)
{
if (obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i].RowState != DataRowState.Deleted)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@OrderID", SqlDbType.VarChar, 9).Value = obj_mdlPurchaseOrder.PK_PurchaseOrder;
//Replace(.Rows(I).Item("Product").ToString(), "-", "")
cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 6).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["ID"].ToString().Replace("-", "");
cmd.Parameters.Add("@StockType", SqlDbType.VarChar, 50).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["Type"].ToString();
cmd.Parameters.Add("@BatchNo", SqlDbType.VarChar, 15).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["Batch No"].ToString();
cmd.Parameters.Add("@ExpiryDate", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["Exp. Date"].ToString();
cmd.Parameters.Add("@MfgDate", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["Mfg. Date"].ToString();
cmd.Parameters.Add("@TP", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["FlatRate"].ToString();
cmd.Parameters.Add("@SaleTaxValue", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["STaxValue"].ToString();
cmd.Parameters.Add("@Quantity", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["Qty"].ToString();
cmd.Parameters.Add("@DiscountPer", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["Disc %"].ToString();
cmd.Parameters.Add("@Discount", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["Discount"].ToString();
cmd.Parameters.Add("@SaleTaxPer", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["SaleTaxPer"].ToString();
cmd.Parameters.Add("@Saletax", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["Sale Tax"].ToString();
cmd.Parameters.Add("@GrossValue", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["GrossValue"].ToString();
cmd.Parameters.Add("@Net", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["Net"].ToString();
cmd.Parameters.Add("@CostRate", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["CostRate"].ToString();
cmd.Parameters.Add("@FlatRate", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["TP"].ToString();
cmd.Parameters.Add("@AvgRate", SqlDbType.Decimal).Value = obj_mdlPurchaseOrder.dtPurchaseOrder.Rows[i]["AvgRate"].ToString();
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 12).Value = "Save";
cmd.ExecuteNonQuery();
}
cmd.Parameters.Clear();
}
}
return cmd;
}