private string InsertUpdatePurchaseDb(clsPurchase st, DataTable dt, string insertUpdateStatus)
{
string returnId = "0";
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["ADO"].ConnectionString;
using (SqlConnection con = new SqlConnection(connection))
{
try
{
con.Open();
using (SqlCommand cmd = new SqlCommand("spInsertUpdatePurchase", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add("@PurchaseId", SqlDbType.Int).Value = st.PurchaseId;
cmd.Parameters.Add("@PurchaseDate", SqlDbType.DateTime).Value = st.PurchaseDate;
cmd.Parameters.Add("@ReferenceNumber", SqlDbType.Int).Value = st.ReferenceNumber;
cmd.Parameters.Add("@dtPurchaseLine", SqlDbType.Structured).Value = dt;
cmd.Parameters.Add("@InsertUpdateStatus", SqlDbType.NVarChar).Value = insertUpdateStatus;
cmd.Parameters.Add("@CheckReturn", SqlDbType.NVarChar, 300).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
returnId = cmd.Parameters["@CheckReturn"].Value.ToString();
cmd.Dispose();
}
con.Close();
con.Dispose();
}
catch (Exception ex)
{
returnId = ex.Message.ToString();
}
}
return(returnId);
}
public ActionResult AddUpdatePurchase(int id = 0)
{
clsPurchase country = new clsPurchase();
if (id > 0)
{
country = (from c in db.tblPurchases
where c.PurchaseId == id
select new clsPurchase
{
PurchaseId = c.PurchaseId,
PurchaseDate = c.PurchaseDate,
ReferenceNumber = c.ReferenceNumber,
ItemName = db.tblPurchaseLines.Where(x => x.PurchaseId == c.PurchaseId).Select(x => x.ItemName).FirstOrDefault(),
Qyt = db.tblPurchaseLines.Where(x => x.PurchaseId == c.PurchaseId).Select(x => x.Qyt).FirstOrDefault(),
Rate = db.tblPurchaseLines.Where(x => x.PurchaseId == c.PurchaseId).Select(x => x.Rate).FirstOrDefault()
}).FirstOrDefault();
}
else
{
country = new clsPurchase
{
PurchaseId = 0,
PurchaseDate = System.DateTime.Now,
ReferenceNumber = 0
};
}
return(PartialView(country));
}
public static ArrayList queryPurchase(string sql)
{
ArrayList purchases = new ArrayList();
try
{
openDatabaseConnection();
mdb.Open();
OleDbCommand cmd;
cmd = new OleDbCommand(sql, mdb);
OleDbDataReader rdr;
rdr = cmd.ExecuteReader();
while (rdr.Read() == true)
{
clsPurchase newPurchases = new clsPurchase((int)rdr["PurchaseID"], (decimal)rdr["Cost"], (string)rdr["PaymentType"], (DateTime)rdr["PaymentDate"]);
purchases.Add(newPurchases);
}
rdr.Close();
}
catch (Exception ex)
{
MessageBox.Show("There was an unexpected problem reading from the DB: " +
ex.Message);
}
finally
{
closeDatabaseConnection();
}
return(purchases);
}
private void populatePurchases()
{
//Refresh the list of purchases...
cboPurchase.Items.Clear();
clsLists.mPurchase = clsSQL.queryPurchase("SELECT * FROM Purchase;");
foreach (clsPurchase purchase in clsLists.mPurchase)
{
clsPurchase p = (clsPurchase)purchase;
clsComboBoxItem item = new clsComboBoxItem(p.ID, "#" + p.ID.ToString() + "( " + p.PaymentDate.ToString());
cboPurchase.Items.Add(item);
}
}
//This is the code needed to update the Purchases info.
//=====================================================
private void btnMediaUpdate_Click(object sender, EventArgs e)
{
{
int index = lstPurchase.SelectedIndex - 2;
if (index < 0 || index >= clsLists.mPurchase.Count)
{
ShowMessage("Please select a valid client in the list to UPDATE that client.");
}
else
{
decimal cost;
string sql;
int locUpdate = lstPurchase.SelectedIndex - 1;
if (validateInput(txtCost, new decimal(1L), new decimal(100000L), out cost) == false || validateInput(txtPurchaseDate) == false)
{
return;
}
if (cboPurchase.Text == "")
{
ShowMessage("Please select an investor");
}
if (MessageBox.Show("Please confirm that you wish to update this record." + txtCost.Text, "Confirm update", MessageBoxButtons.YesNo) == DialogResult.No)
{
MessageBox.Show("Request Ignored");
lstPurchase.SelectedIndex = -1;
return;
}
else
{
//clsComboBoxItem item = (clsComboBoxItem)cboPurchase.SelectedItem;
string mediaPurchase = cboPurchase.Text;
clsPurchase file = (clsPurchase)clsLists.mPurchase[locUpdate];
sql = "UPDATE Purchase SET "
+ "Cost = " + clsSQL.ToSql(cost) + ", "
+ "PaymentType = " + clsSQL.ToSql(mediaPurchase) + ", "
+ "PaymentDate = " + clsSQL.ToSql(txtPurchaseDate.Text)
+ "WHERE PurchaseID = " + file.ID;
clsSQL.nonQuery(sql);
clearPurchases();
//review the data
readPurchases("SELECT * FROM Purchase");
}
}
}
}
//This helper method selects the data in the Purchase applcation
//==============================================================
private void lstPurchase_SelectedIndexChanged(object sender, EventArgs e)
{
int loc = lstPurchase.SelectedIndex - 1;
if (loc < 0 || loc >= clsLists.mPurchase.Count)
{
return;
}
clsPurchase selectedPurchase = (clsPurchase)clsLists.mPurchase[loc];
txtCost.Text = selectedPurchase.cost.ToString();
cboPurchase.Text = selectedPurchase.PaymentType;
txtPurchaseDate.Text = selectedPurchase.PaymentDate.ToString("d");
MessageBox.Show("If you want to update this record, change the details and click 'Update'.\n\nIf you want to delete this record, click 'Delete'.");
}
//This is the Delete Button Method used to delete the Purchase data in the application.
//=====================================================================================
private void btnMediaDelete_Click(object sender, EventArgs e)
{
int index = lstPurchase.SelectedIndex - 2;
if (index < 0 || index >= clsLists.mPurchase.Count)
{
ShowMessage("Please select a valid client in the list to DELETE that client.");
}
else
{
clsPurchase clsPurchase = (clsPurchase)clsLists.mPurchase[index];
string sql = "DELETE FROM Purchase WHERE PurchaseID = " + clsPurchase.ID + ";";
clsSQL.nonQuery(sql);
ShowMessage("Purchase has been removed");
readPurchases("SELECT * FROM Purchase");
}
}
public ActionResult DeletePurchase(int id)
{
string message = "";
bool status = false;
DataTable dtPurchase = new DataTable();
dtPurchase.Columns.Add("Id");
dtPurchase.Columns.Add("ItemName");
dtPurchase.Columns.Add("Qyt");
dtPurchase.Columns.Add("Rate");
dtPurchase.Rows.Add(new object[] { 0, "", 0, 0 });
clsPurchase st = new clsPurchase();
st.PurchaseId = id;
string returnId = InsertUpdatePurchaseDb(st, dtPurchase, "Delete");
if (returnId == "Success")
{
ModelState.Clear();
status = true;
message = "Successfully Deleted";
}
else
{
ModelState.Clear();
status = false;
message = returnId;
}
return(new JsonResult {
Data = new { status = status, message = message }
});
}
public ActionResult AddUpdatePurchase(clsPurchase purchase, string childData)
{
string message = "";
bool status = false;
try
{
List <clsPurchase> PurchaseList = JsonConvert.DeserializeObject <List <clsPurchase> >(childData);
//data table for Branch Starts
DataTable dtPurchase = new DataTable();
dtPurchase.Columns.Add("Id");
dtPurchase.Columns.Add("ItemName");
dtPurchase.Columns.Add("Qyt");
dtPurchase.Columns.Add("Rate");
if (PurchaseList.Count != 0)
{
for (int i = 0; i < PurchaseList.Count; i++)
{
dtPurchase.Rows.Add(new object[] { i + 1, PurchaseList[i].ItemName, PurchaseList[i].Qyt, PurchaseList[i].Rate });
}
}
else
{
dtPurchase.Rows.Add(new object[] { 0, "", 0, 0 });
}
string returnId = "0";
string insertUpdateStatus = "";
if (purchase.PurchaseId > 0)
{
insertUpdateStatus = "Update";
}
else
{
insertUpdateStatus = "Save";
}
returnId = InsertUpdatePurchaseDb(purchase, dtPurchase, insertUpdateStatus);
if (returnId == "Success")
{
status = true;
message = "User Type Successfully Updated";
}
else
{
status = false;
message = returnId;
}
}
catch (Exception ex)
{
status = false;
message = ex.Message.ToString();
}
return(new JsonResult {
Data = new { status = status, message = message }
});
}
