public bool SavePurchaseInvoice(mdlPurchaseInvoice obj_mdlPurchaseInvoice, string Operation)
{
bool IsSaved = false;
if (con.State == ConnectionState.Open)
{
con.Close();
}
con.Open();
SqlTransaction transaction = con.BeginTransaction();
SqlCommand cmd = new SqlCommand("stp_ERP_PurchaseInvoice_Master", con, transaction);
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Pk_InvoiceID", SqlDbType.VarChar, 9).Value = obj_mdlPurchaseInvoice.PK_InvoiceID;
cmd.Parameters.Add("@InvoiceDate", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseInvoice.InvoiceDate;
cmd.Parameters.Add("@CompanyInvID", SqlDbType.VarChar, 15).Value = obj_mdlPurchaseInvoice.CompanyInvID;
cmd.Parameters.Add("@CompanyInvDate", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseInvoice.CompanyInvDate;
cmd.Parameters.Add("@OurRefNo", SqlDbType.VarChar, 15).Value = obj_mdlPurchaseInvoice.OurRefNo;
cmd.Parameters.Add("@CompRefNo", SqlDbType.VarChar, 15).Value = obj_mdlPurchaseInvoice.CompRefNo;
cmd.Parameters.Add("@SupplierID", SqlDbType.VarChar, 3).Value = obj_mdlPurchaseInvoice.SupplierID;
cmd.Parameters.Add("@DueDate", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseInvoice.DueDate;
cmd.Parameters.Add("@GatePassNo", SqlDbType.VarChar, 50).Value = obj_mdlPurchaseInvoice.GatePassNo;
cmd.Parameters.Add("@POID", SqlDbType.VarChar, 9).Value = obj_mdlPurchaseInvoice.POID;
cmd.Parameters.Add("@GRN", SqlDbType.VarChar, 9).Value = obj_mdlPurchaseInvoice.GRN;
cmd.Parameters.Add("@Status", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseInvoice.Status;
cmd.Parameters.Add("@GrossAmount", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.GrossAmount;
cmd.Parameters.Add("@Discount", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.Discount;
cmd.Parameters.Add("@Saletax", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.SaleTax;
cmd.Parameters.Add("@NetAmnount", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.NetAmount;
cmd.Parameters.Add("@PIVNo", SqlDbType.VarChar, 11).Value = obj_mdlPurchaseInvoice.NetAmount;
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar, 6).Value = obj_mdlPurchaseInvoice.CustomerID;
cmd.Parameters.Add("@AddUser", SqlDbType.VarChar, 16).Value = obj_mdlPurchaseInvoice.AddUser;
cmd.Parameters.Add("@PostUser", SqlDbType.VarChar, 16).Value = obj_mdlPurchaseInvoice.Postuser;
cmd.Parameters.Add("@PostDate", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseInvoice.PostDate;
cmd.Parameters.Add("@PostTime", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseInvoice.PostTime;
cmd.Parameters.Add("@Remarks", SqlDbType.VarChar, 50).Value = obj_mdlPurchaseInvoice.Remarks;
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 = SavePurchaseInvoiceDetails(obj_mdlPurchaseInvoice, 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 SavePurchaseInvoiceDetails(mdlPurchaseInvoice obj_mdlPurchaseInvoice, SqlTransaction transaction, SqlCommand cmd)
{
//Deleting Order Details
if (obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows.Count > 0)
{
cmd = new SqlCommand("Delete from D_PurchaseInvoice where InvoiceId ='" + obj_mdlPurchaseInvoice.PK_InvoiceID + "'", con, transaction);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
//Inserting Order Details
if (obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows.Count > 0)
{
cmd = new SqlCommand("stp_ERP_PurchaseInvoice_Detail", con, transaction);
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i <= obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows.Count - 1; i++)
{
if (obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i].RowState != DataRowState.Deleted)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@InvoiceID", SqlDbType.VarChar, 9).Value = obj_mdlPurchaseInvoice.PK_InvoiceID;
cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 6).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["ID"].ToString().Replace("-", "");
cmd.Parameters.Add("@StockType", SqlDbType.VarChar, 50).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["Type"].ToString();
cmd.Parameters.Add("@BatchNo", SqlDbType.VarChar, 15).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["Batch No"].ToString();
cmd.Parameters.Add("@ExpiryDate", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["Exp. Date"].ToString();
cmd.Parameters.Add("@MfgDate", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["Mfg. Date"].ToString();
cmd.Parameters.Add("@TP", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["FlatRate"].ToString();
cmd.Parameters.Add("@STaxValue", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["STaxValue"].ToString();
cmd.Parameters.Add("@Quantity", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["Qty"].ToString();
cmd.Parameters.Add("@DiscountPer", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["Disc %"].ToString();
cmd.Parameters.Add("@Discount", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["Discount"].ToString();
cmd.Parameters.Add("@SaleTaxPer", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["SaleTaxPer"].ToString();
cmd.Parameters.Add("@Saletax", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["Sale Tax"].ToString();
cmd.Parameters.Add("@GrossValue", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["GrossValue"].ToString();
cmd.Parameters.Add("@Net", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["Net"].ToString();
cmd.Parameters.Add("@CostRate", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["CostRate"].ToString();
cmd.Parameters.Add("@FlatRate", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["TP"].ToString();
cmd.Parameters.Add("@AvgRate", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["AvgRate"].ToString();
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 12).Value = "Save";
cmd.ExecuteNonQuery();
}
cmd.Parameters.Clear();
}
}
return cmd;
}
public bool PostPurchaseInvoice(mdlPurchaseInvoice obj_mdlPurchaseInvoice)
{
bool isPosted = false;
if (con.State == ConnectionState.Open)
{
con.Close();
}
con.Open();
SqlTransaction transaction = con.BeginTransaction();
SqlCommand cmd = new SqlCommand("stp_ERP_PurchaseInvoice_Post", con, transaction);
cmd.CommandType = CommandType.StoredProcedure;
try
{
if (obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows.Count > 0)
{
for (int i = 0; i <= obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows.Count - 1; i++)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 6).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["ID"].ToString();
cmd.Parameters.Add("@BatchNo", SqlDbType.VarChar, 15).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["Batch No"].ToString();
cmd.Parameters.Add("@ExpiryDate", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["Exp. Date"].ToString();
cmd.Parameters.Add("@ManfactureDate", SqlDbType.VarChar, 20).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["Mfg. Date"].ToString();
cmd.Parameters.Add("@TP", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["TP"].ToString();
cmd.Parameters.Add("@STaxValue", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["STaxValue"].ToString();
cmd.Parameters.Add("@Quantity", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["Qty"].ToString();
cmd.Parameters.Add("@CostRate", SqlDbType.Decimal).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["CostRate"];
cmd.Parameters.Add("@StockType", SqlDbType.VarChar, 50).Value = obj_mdlPurchaseInvoice.dtPurchaseInvoice.Rows[i]["Type"].ToString();
cmd.Parameters.Add("@AddUser", SqlDbType.VarChar, 16).Value = obj_mdlPurchaseInvoice.AddUser;
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 20).Value = "Verify";
cmd.ExecuteNonQuery();
//---------------------Update Status M_Purchase Invoice -------------------------//
cmd.Parameters.Clear();
cmd.Parameters.Add("@pk_InvoiceId", SqlDbType.VarChar, 9).Value = obj_mdlPurchaseInvoice.PK_InvoiceID;
cmd.Parameters.Add("@AddUser", SqlDbType.VarChar, 16).Value = obj_mdlPurchaseInvoice.AddUser;
cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 20).Value = "UpdateStatusPR";
cmd.ExecuteNonQuery();
}
}
transaction.Commit();
isPosted = 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);
isPosted = false;
}
return isPosted;
}