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