public void SendRate(int id, double Rate, string Comment)
{
Item it = db.Items.Find(id);
try
{
ItemRate ir = db.ItemRates.First(x => x.ItemId == id && x.Username == HttpContext.User.Identity.Name);
it.Rate = (it.Rate * it.ItemRates.Count() - ir.Rate + Rate) / (it.ItemRates.Count());
ir.Rate = Rate;
ir.Comment = Comment;
db.Entry(ir).State = EntityState.Modified;
}
catch
{
db.ItemRates.Add(new ItemRate()
{
ItemId = id,
Username = HttpContext.User.Identity.Name,
Rate = Rate,
Comment = Comment
});
if (it.Rate == null)
{
it.Rate = Rate;
}
else
{
it.Rate = (it.Rate * (it.ItemRates.Count() - 1) + Rate) / it.ItemRates.Count();
}
}
db.SaveChanges();
}
public int ItemRatesDML(ItemRate itemRate)
{
int result = 0;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@ItemRateId", itemRate.ItemRatesId));
paramCollection.Add(new DBParameter("@VendorId", itemRate.VendorId));
paramCollection.Add(new DBParameter("@ItemId", itemRate.ItemId));
paramCollection.Add(new DBParameter("@Quantity", itemRate.Quantity));
paramCollection.Add(new DBParameter("@UnitId", itemRate.UnitId));
paramCollection.Add(new DBParameter("@Price", itemRate.Price));
paramCollection.Add(new DBParameter("@Shipping", itemRate.Shipping));
paramCollection.Add(new DBParameter("@Excise", itemRate.Excise));
paramCollection.Add(new DBParameter("@CST", itemRate.CST));
paramCollection.Add(new DBParameter("@VAT", itemRate.VAT));
paramCollection.Add(new DBParameter("@Insurance", itemRate.Insurance));
paramCollection.Add(new DBParameter("@Freight", itemRate.Freight));
paramCollection.Add(new DBParameter("@TotalPrice", itemRate.TotalPrice));
paramCollection.Add(new DBParameter("@Flag", itemRate.Flag));
result = _DBHelper.ExecuteNonQuery("SpPrchsItemRatesDML", paramCollection, CommandType.StoredProcedure);
}
catch (Exception ex)
{
}
return(result);
}
public ActionResult RateItem(int id)
{
try
{
ItemRate ir = db.ItemRates.First(x => x.ItemId == id && x.Username == HttpContext.User.Identity.Name);
ViewBag.Rate = ir.Rate;
ViewBag.Comment = ir.Comment;
}
catch
{
}
return(PartialView("../Partial/_Rate", db.Items.Find(id)));
}
private void UpdateList(int id)
{
DS = new DataSet();
itemRate = new ItemRate();
purchaseData = new PurchaseData();
itemRate.VendorId = id;
itemRate.Flag = 1; //1 for get by VendorId
DS = purchaseData.GetItemRateList(itemRate);
rpItemRatesList.DataSource = DS;
rpItemRatesList.DataBind();
SelDropDown();
}
protected void dpItems_SelectedIndexChanged(object sender, EventArgs e)
{
DS = new DataSet();
itemRate = new ItemRate();
purchaseData = new PurchaseData();
itemRate.ItemId = Convert.ToInt32(dpItems.SelectedItem.Value);
itemRate.Flag = 3; //
DS = purchaseData.GetItemRateList(itemRate);
rpItemRatesList.DataSource = DS;
rpItemRatesList.DataBind();
ScriptManager.RegisterStartupScript(this, this.GetType(), "sel1", "$('#MainContent_dpItems').addClass('selectpicker');$('#MainContent_dpItems').selectpicker();", true);
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
itemRate = new ItemRate();
purchaseData = new PurchaseData();
itemRate.ItemRatesId = Convert.ToInt32(hfItemRatesId.Value);
itemRate.VendorId = Convert.ToInt32(dpVendor1.SelectedItem.Value);
itemRate.ItemId = Convert.ToInt32(dpItem.SelectedItem.Value);
itemRate.Quantity = string.IsNullOrEmpty(txtQuantity.Text.ToString()) ? 0 : Convert.ToDouble(txtQuantity.Text);
itemRate.UnitId = Convert.ToInt32(dpUnit.SelectedItem.Value);
itemRate.Quantity = string.IsNullOrEmpty(txtQuantity.Text.ToString()) ? 0 : Convert.ToDouble(txtQuantity.Text);
itemRate.Price = string.IsNullOrEmpty(txtPrice.Text.ToString()) ? 0 : Convert.ToDecimal(txtPrice.Text);
itemRate.Shipping = string.IsNullOrEmpty(txtShipping.Text.ToString()) ? 0 : Convert.ToDecimal(txtShipping.Text);
itemRate.Excise = string.IsNullOrEmpty(txtExcise.Text.ToString()) ? 0 : Convert.ToDouble(txtExcise.Text);
itemRate.CST = string.IsNullOrEmpty(txtCst.Text.ToString()) ? 0 : Convert.ToDouble(txtCst.Text);
itemRate.VAT = string.IsNullOrEmpty(txtVat.Text.ToString()) ? 0 : Convert.ToDouble(txtVat.Text);
itemRate.Insurance = string.IsNullOrEmpty(txtInsurance.Text.ToString()) ? 0 : Convert.ToDecimal(txtInsurance.Text);
itemRate.Freight = string.IsNullOrEmpty(txtFreight.Text.ToString()) ? 0 : Convert.ToDecimal(txtFreight.Text);
itemRate.TotalPrice = string.IsNullOrEmpty(txtTotalPrice.Text.ToString()) ? 0 : Convert.ToDecimal(txtTotalPrice.Text);
itemRate.Flag = 2; //1 for Update
int Result = 0;
Result = purchaseData.ItemRatesDML(itemRate);
if (Result > 0)
{
// ScriptManager.RegisterStartupScript(Page, Page.GetType(), "myModal", "$('.modal-backdrop').removeClass(.modal-backdrop);", true);
divDanger.Visible = false;
divwarning.Visible = false;
divSusccess.Visible = true;
lblSuccess.Text = "Item Rate Updated Successfully";
ClearTextBox();
pnlError.Update();
UpdateList(itemRate.VendorId);
upMain.Update();
}
else
{
divDanger.Visible = false;
divwarning.Visible = true;
divSusccess.Visible = false;
lblwarning.Text = "Please Contact to Site Admin";
pnlError.Update();
}
}
public DataSet GetItemRateList(ItemRate itemRate)
{
dbPurchase = new DBPurchase();
DS = new DataSet();
try
{
DS = dbPurchase.GetItemRateList(itemRate);
return(DS);
}
catch (Exception)
{
throw;
}
}
public int ItemRatesDML(ItemRate itemRate)
{
dbPurchase = new DBPurchase();
int Result = 0;
try
{
Result = dbPurchase.ItemRatesDML(itemRate);
return(Result);
}
catch (Exception)
{
throw;
}
}
private void GetDetailsById(int itemRateId)
{
itemRate = new ItemRate();
purchaseData = new PurchaseData();
DataSet DS = new DataSet();
itemRate.ItemRatesId = itemRateId;
itemRate.Flag = 2;
DS = purchaseData.GetItemRateList(itemRate);
if (!Comman.Comman.IsDataSetEmpty(DS))
{
dpVendor1.ClearSelection();
if (dpVendor1.Items.FindByValue(Convert.ToInt32(DS.Tables[0].Rows[0]["VendorId"]).ToString()) != null)
{
dpVendor1.Items.FindByValue(Convert.ToInt32(DS.Tables[0].Rows[0]["VendorId"]).ToString()).Selected = true;
}
dpItem.ClearSelection();
if (dpItem.Items.FindByValue(Convert.ToInt32(DS.Tables[0].Rows[0]["ItemId"]).ToString()) != null)
{
dpItem.Items.FindByValue(Convert.ToInt32(DS.Tables[0].Rows[0]["ItemId"]).ToString()).Selected = true;
}
txtQuantity.Text = string.IsNullOrEmpty(DS.Tables[0].Rows[0]["Quantity"].ToString()) ? string.Empty : DS.Tables[0].Rows[0]["Quantity"].ToString();
dpUnit.ClearSelection();
if (dpUnit.Items.FindByValue(Convert.ToInt32(DS.Tables[0].Rows[0]["UnitId"]).ToString()) != null)
{
dpUnit.Items.FindByValue(Convert.ToInt32(DS.Tables[0].Rows[0]["UnitId"]).ToString()).Selected = true;
}
txtPrice.Text = string.IsNullOrEmpty(DS.Tables[0].Rows[0]["Price"].ToString()) ? string.Empty : Convert.ToDecimal(DS.Tables[0].Rows[0]["Price"]).ToString("#0.00");
txtExcise.Text = string.IsNullOrEmpty(DS.Tables[0].Rows[0]["ExciseDuty"].ToString()) ? string.Empty : DS.Tables[0].Rows[0]["ExciseDuty"].ToString();
txtShipping.Text = string.IsNullOrEmpty(DS.Tables[0].Rows[0]["Shipping"].ToString()) ? string.Empty : DS.Tables[0].Rows[0]["Shipping"].ToString();
txtCst.Text = string.IsNullOrEmpty(DS.Tables[0].Rows[0]["CST"].ToString()) ? string.Empty : DS.Tables[0].Rows[0]["CST"].ToString();
txtVat.Text = string.IsNullOrEmpty(DS.Tables[0].Rows[0]["Vat"].ToString()) ? string.Empty : DS.Tables[0].Rows[0]["Vat"].ToString();
txtInsurance.Text = string.IsNullOrEmpty(DS.Tables[0].Rows[0]["Insurance"].ToString()) ? string.Empty : Convert.ToDecimal(DS.Tables[0].Rows[0]["Insurance"]).ToString("#0.00");
txtFreight.Text = string.IsNullOrEmpty(DS.Tables[0].Rows[0]["FreightCharges"].ToString()) ? string.Empty : Convert.ToDecimal(DS.Tables[0].Rows[0]["FreightCharges"]).ToString("#0.00");
txtTotalPrice.Text = string.IsNullOrEmpty(DS.Tables[0].Rows[0]["TotalPrice"].ToString()) ? string.Empty : Convert.ToDecimal(DS.Tables[0].Rows[0]["TotalPrice"]).ToString("#0.00");
}
btnSubmit.Visible = false;
btnUpdate.Visible = true;
}
public DataSet GetItemRateList(ItemRate itemRate)
{
DS = new DataSet();
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@ItemRatesId", itemRate.ItemRatesId));
paramCollection.Add(new DBParameter("@VendorId", itemRate.VendorId));
paramCollection.Add(new DBParameter("@ItemId", itemRate.ItemId));
paramCollection.Add(new DBParameter("@Flag", itemRate.Flag));
DS = _DBHelper.ExecuteDataSet("SpPrchsItemRatesGet", paramCollection, CommandType.StoredProcedure);
}
catch (Exception)
{
throw;
}
return(DS);
}
public IHttpActionResult shitTable(String oldTableId, String newTableId, String shifType)
{
Boolean valid = false;
var oldTableKOTs = new List <PrintEntity>();
SqlConnection connection = new SqlConnection(connectionString);
//get all active KOTIDs
String query = "select KOTNO from TrnHdrKOT where TblID = '" + oldTableId + "' and Billed ='N' order by KOTNO asc";
try
{
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var kotData = new PrintEntity();
kotData.kotNumber = reader.GetInt32(0);
oldTableKOTs.Add(kotData);
}
valid = true;
}
catch (Exception ex)
{
valid = false;
}
finally
{
connection.Close();
}
// Update new table Id in TrnHdrKot table
for (var i = 0; i < oldTableKOTs.ToArray().Length; i++)
{
String query2 = "update TrnHdrKOT set TblID = " + newTableId + " where KOTNO= '" + oldTableKOTs[i].kotNumber + "' ";
try
{
SqlCommand cmd = new SqlCommand(query2, connection);
connection.Open();
cmd.ExecuteNonQuery();
valid = true;
}
catch (Exception ex)
{
valid = false;
}
finally
{
connection.Close();
}
var itemList = new List <ItemRate>();
// get ItemId and PCATID for updating item rate in TrnDtlKOT
String query3 = " select d.ItemID,d.KOTQty, i.PCatID from TrnDtlKOT as d, MstItemRate as i where d.KOTNO = '" + oldTableKOTs[i].kotNumber + "' and d.ItemID = i.ItemID";
try
{
SqlCommand cmd = new SqlCommand(query3, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var itemData = new ItemRate();
itemData.itemId = reader.GetInt32(0);
itemData.KOTQuantity = reader.GetDecimal(1);
itemData.PCATID = reader.GetInt32(2);
itemList.Add(itemData);
}
valid = true;
}
catch (Exception ex)
{
valid = false;
}
finally
{
connection.Close();
}
for (var j = 0; j < itemList.Count; j++)
{
// update item rates in TrnDltKOT table using KOTNO
String query4 = " update TrnDtlKOT set KOTRate = (select tr.ItemRate from MstItemRate as tr where tr.ItemID = '" + itemList[i].itemId + "' and tr.PCatID ='" + itemList[i].PCATID + "'), KOTAmt = " + itemList[i].KOTQuantity + " * (select tr.ItemRate from MstItemRate as tr where tr.ItemID = '" + itemList[i].itemId + "' and tr.PCatID ='" + itemList[i].PCATID + "') where KOTNO = '" + oldTableKOTs[i].kotNumber + "' and ItemID = '" + itemList[i].itemId + "' ";
try
{
SqlCommand cmd = new SqlCommand(query4, connection);
connection.Open();
cmd.ExecuteNonQuery();
valid = true;
}
catch (Exception ex)
{
valid = false;
}
finally
{
connection.Close();
}
}
}
// update table status after shift
String query5 = "";
if (shifType == "merge")
{
query5 = " update MstTBL set TblStatus='V' where TBLID= '" + oldTableId + "'; ";
}
if (shifType == "shift")
{
query5 = " update MstTBL set TblStatus='V' where TBLID= '" + oldTableId + "'; update MstTBL set TblStatus= 'O' where TBLID='" + newTableId + "'; ";
}
try
{
SqlCommand cmd = new SqlCommand(query5, connection);
connection.Open();
cmd.ExecuteNonQuery();
valid = true;
}
catch (Exception ex)
{
valid = false;
}
finally
{
connection.Close();
}
if (valid)
{
if (shifType == "merge")
{
return(Content(HttpStatusCode.OK, "Table Merged Successfully"));
}
if (shifType == "shift")
{
return(Content(HttpStatusCode.OK, "Table Shifted Successfully"));
}
}
else
{
if (shifType == "merge")
{
return(Content(HttpStatusCode.NotFound, "Error Merging Table"));
}
if (shifType == "shift")
{
return(Content(HttpStatusCode.NotFound, "Error Shifting Table"));
}
}
return(Content(HttpStatusCode.NotFound, "Error, Try again"));
}
public LineItemBuilder<T> HasContributingItem()
{
this.itemRate = null;
return this;
}
public LineItemBuilder<T> WithRate(ItemRate rate)
{
this.itemRate = rate;
return this;
}
public ContributingItemBuilder <T> WithRate(ItemRate rate)
{
this.itemRate = rate;
return(this);
}