public ActionResult OpeningStock()
{
OpeningStock OpeningStock = new OpeningStock();
OpeningStock.OpeningStockItem = new OpeningStockRepository().GetItem();
return(PartialView("_OpeningStock", OpeningStock));
}
private async Task <OpeningStock> CreateOpeningStock(OpeningStock EDops)
{
using (var ctx = new OpeningStockService())
{
return(await ctx.CreateOpeningStock(EDops).ConfigureAwait(false));
}
}
public ActionResult OpeningStockList(int?stockpointId)
{
FillItem();
FillPartNo();
OpeningStock OpeningStock = new OpeningStock();
OpeningStock.OpeningStockItem = new List <OpeningStockItem>();
if (stockpointId == null || stockpointId == 0)
{
var OpeningStockItem = new OpeningStockItem {
Quantity = 0
};
OpeningStock.OpeningStockItem.Add(OpeningStockItem);
}
else
{
var repo = new OpeningStockRepository();
OpeningStock.OpeningStockItem = repo.GetItem(stockpointId).ToList();
}
if (OpeningStock.OpeningStockItem.Count == 0)
{
OpeningStock.OpeningStockItem.Add(new OpeningStockItem());
}
return(PartialView("OpeningStockList", OpeningStock));
}
//private void btn_opening_stock_Click(object sender, RoutedEventArgs e)
//{
// StockTransferOut os = new StockTransferOut();
// this.FinposContainer.Navigate(os);
//}
private void btn_opening_stock_Click(object sender, RoutedEventArgs e)
{
e.Handled = true;
OpeningStock os = new OpeningStock();
this.FinposContainer.Navigate(os);
}
public int InsertOpeningStock(OpeningStock objOpeningStock)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
int id = 0;
foreach (var item in objOpeningStock.OpeningStockItem)
{
if (item.ItemId == null || item.ItemId == 0)
{
continue;
}
string sql = @"insert into OpeningStock(StockPointId,ItemId,Quantity,CreatedBy,CreatedDate,OrganizationId)
Values (@stockpointId,@ItemId,@Quantity,@CreatedBy,@CreatedDate,@OrganizationId);
SELECT CAST(SCOPE_IDENTITY() as int)";
//id = connection.Query<int>(sql, objOpeningStock).Single();
id = connection.Query <int>(sql, new
{
stockpointId = objOpeningStock.stockpointId,
ItemId = item.ItemId,
Quantity = item.Quantity,
CreatedBy = objOpeningStock.CreatedBy,
CreatedDate = objOpeningStock.CreatedDate,
OrganizationId = objOpeningStock.OrganizationId
}).Single();
InsertLoginHistory(dataConnection, objOpeningStock.CreatedBy, "Create", "Opening Stock", id.ToString(), "0");
}
return(id);
}
}
private void OKkryptonButton_Click(object sender, EventArgs e)
{
if (gridData.SelectedRows.Count > 0)
{
OPENING_STOCK = (OpeningStock)gridData.SelectedRows[0].Tag;
this.Close();
}
}
private void gridData_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
{
if (gridData.SelectedRows.Count > 0)
{
OPENING_STOCK = (OpeningStock)gridData.SelectedRows[0].Tag;
this.Close();
}
}
public HttpResponseMessage Delete([FromUri] int Id, [FromBody] int modifiedBy)
{
OpeningStock s = new OpeningStock();
s.ID = Id;
s.ModifiedBy = modifiedBy;
return(Request.CreateResponse(HttpStatusCode.OK, s.Delete()));
}
public int DeleteStockUpdate(OpeningStock objOpeningStock)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
string sql = @"Delete StockUpdate OUTPUT DELETED.StockPointId WHERE StockPointId=@StockPointId AND StockType='OpeningStock'";
var id = connection.Execute(sql, objOpeningStock);
return(id);
}
}
private void msbTrans_ProdUnit_OpeningStock_Click(object sender, EventArgs e)
{
if (childOpeningStock == null || childOpeningStock.Text == "")
{
childOpeningStock = new OpeningStock(2);
childOpeningStock.MdiParent = this;
childOpeningStock.Show();
}
}
private void pricemovkryptonButton1_Click(object sender, EventArgs e)
{
try
{
pricemovementkryptonDataGridView1.Rows.Clear();
if (m_part.ID == 0)
{
return;
}
IList movs = r_sir.GetSupplierInvoiceItem(m_part.ID);
foreach (EventItem itm in movs)
{
int r = pricemovementkryptonDataGridView1.Rows.Add();
pricemovementkryptonDataGridView1[dateprcmovColumn1.Index, r].Value = itm.EVENT.TRANSACTION_DATE;
pricemovementkryptonDataGridView1[codeprcmovColumn2.Index, r].Value = itm.EVENT.CODE;
pricemovementkryptonDataGridView1[typeprcmovColumn1.Index, r].Value = itm.STOCK_CARD_ENTRY_TYPE.ToString();
pricemovementkryptonDataGridView1[qtyprcmovColumn1.Index, r].Value = itm.GetAmountInSmallestUnit();
pricemovementkryptonDataGridView1[unitprcmovColumn3.Index, r].Value = m_part.UNIT.CODE;
switch (itm.STOCK_CARD_ENTRY_TYPE)
{
case StockCardEntryType.SupplierInvoice:
SupplierInvoiceItem sii = (SupplierInvoiceItem)itm;
SupplierInvoice si = (SupplierInvoice)sii.EVENT;
si.SUPPLIER = (Supplier)r_sup.GetById(si.SUPPLIER);
pricemovementkryptonDataGridView1[vendorprcmovColumn4.Index, r].Value = si.SUPPLIER.NAME;
double c = sii.SUBTOTAL / sii.GetAmountInSmallestUnit();
c = r_ccy.ConvertToCurrency(si.CURRENCY, m_part.CURRENCY, c, si.TRANSACTION_DATE);
pricemovementkryptonDataGridView1[priceprcmovColumn.Index, r].Value = c < 0 ? -c : c;
break;
case StockCardEntryType.StockTaking:
StockTakingItems stk = (StockTakingItems)itm;
StockTaking stkh = (StockTaking)itm.EVENT;
double p = stk.TOTAL_AMOUNT / stk.GetAmountInSmallestUnit();
p = r_ccy.ConvertToCurrency(stkh.CURRENCY, m_part.CURRENCY, p, stkh.TRANSACTION_DATE);
pricemovementkryptonDataGridView1[priceprcmovColumn.Index, r].Value = p < 0 ? -p : p;
break;
case StockCardEntryType.OpeningStock:
OpeningStockItem opn = (OpeningStockItem)itm;
OpeningStock opnh = (OpeningStock)itm.EVENT;
double x = opn.TOTAL_AMOUNT / opn.GetAmountInSmallestUnit();
x = r_ccy.ConvertToCurrency(opnh.CURRENCY, m_part.CURRENCY, x, opnh.TRANSACTION_DATE);
pricemovementkryptonDataGridView1[priceprcmovColumn.Index, r].Value = x < 0 ? -x : x;
break;
}
pricemovementkryptonDataGridView1[statusMovementColumn.Index, r].Value = itm.EVENT.POSTED.ToString();
}
UserSetting.AddNumberToGrid(pricemovementkryptonDataGridView1);
updatePriceMovement();
}
catch (Exception x)
{
KryptonMessageBox.Show(x.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public int DeleteOpeningStock(OpeningStock objOpeningStock)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
string sql = @"Delete OpeningStock OUTPUT DELETED.StockPointId WHERE StockPointId=@StockPointId";
var id = connection.Execute(sql, objOpeningStock);
InsertLoginHistory(dataConnection, objOpeningStock.CreatedBy, "Update", "Opening Stock", id.ToString(), "0");
return(id);
}
}
public async Task SaveOpeningStock(OpeningStock i)
{
if (i == null)
{
return;
}
using (var ctx = new OpeningStockService())
{
await ctx.UpdateOpeningStock(i).ConfigureAwait(false);
}
}
public ActionResult Create()
{
FillStockpoint();
OpeningStock OpeningStock = new OpeningStock();
OpeningStock.OpeningStockItem = new List <OpeningStockItem>();
OpeningStock.OpeningStockItem.Add(new OpeningStockItem());
return(View(OpeningStock));
}
public HttpResponseMessage Save(OpeningStock s)
{
if (s.ID > 0)
{
return(Request.CreateResponse(HttpStatusCode.OK, s.Update()));
}
else
{
return(Request.CreateResponse(HttpStatusCode.OK, s.Save()));
}
}
public void ClearForm()
{
try
{
textBoxCode.Text = "";
dateKryptonDateTimePicker.Value = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
employeeKryptonComboBox.SelectedIndex = 0;
warehousekryptonComboBox.SelectedIndex = 0;
currencyKryptonComboBox.SelectedIndex = 0;
totalAmountkryptonNumericUpDown.Value = 0;
notesKryptonTextBox.Text = "";
dataItemskryptonDataGridView.Rows.Clear();
m_stocktaking = new OpeningStock();
errorProvider1.Clear();
}
catch (Exception x)
{
KryptonMessageBox.Show(x.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void searchToolStripButton_Click(object sender, EventArgs e)
{
IList result = searchToolStripTextBox.Text == string.Empty?new ArrayList() : r_openingstock.Search(searchToolStripTextBox.Text);
if (result.Count == 1)
{
m_stocktaking = (OpeningStock)result[0];
m_stocktaking = (OpeningStock)r_openingstock.Get(m_stocktaking.ID);
m_stocktaking.EMPLOYEE = (Employee)r_employee.GetById(m_stocktaking.EMPLOYEE);
m_stocktaking.WAREHOUSE = (Warehouse)r_warehouse.GetById(m_stocktaking.WAREHOUSE);
m_stocktaking.CURRENCY = (Currency)r_ccy.GetById(m_stocktaking.CURRENCY);
loadData();
setEnableForm(false);
setEditMode(EditMode.View);
}
else
{
using (SearchOpeningStockForm frm = new SearchOpeningStockForm(searchToolStripTextBox.Text, result))
{
frm.ShowDialog();
if (frm.OPENING_STOCK == null)
{
return;
}
else
{
m_stocktaking = frm.OPENING_STOCK;
m_stocktaking = (OpeningStock)r_openingstock.Get(m_stocktaking.ID);
m_stocktaking.EMPLOYEE = (Employee)r_employee.GetById(m_stocktaking.EMPLOYEE);
m_stocktaking.WAREHOUSE = (Warehouse)r_warehouse.GetById(m_stocktaking.WAREHOUSE);
m_stocktaking.CURRENCY = (Currency)r_ccy.GetById(m_stocktaking.CURRENCY);
loadData();
setEnableForm(false);
setEditMode(EditMode.View);
}
}
}
}
public ActionResult Save(OpeningStock model)
{
model.OrganizationId = OrganizationId;
model.CreatedDate = System.DateTime.Now;
model.CreatedBy = UserID.ToString();
//new OpeningStockRepository().DeleteOpeningStock(model);
//new OpeningStockRepository().InsertOpeningStock(model);
//new OpeningStockRepository().DeleteStockUpdate(model);
//new OpeningStockRepository().InsertStockUpdate(model);
//new OpeningStockRepository().UpdateOpeningStock(model);
new OpeningStockRepository().SaveOpeningStock(model);
FillStockpoint();
FillItem();
FillPartNo();
TempData["Success"] = "Added Successfully!";
OpeningStock OpeningStock = new OpeningStock();
OpeningStock.OpeningStockItem = new List <OpeningStockItem>();
OpeningStock.OpeningStockItem.Add(new OpeningStockItem());
return(RedirectToAction("Index"));
}
public int InsertStockUpdate(OpeningStock objOpeningStock, IDbConnection connection, IDbTransaction txn)
{
//using (IDbConnection connection = OpenConnection(dataConnection))
//{
int id = 0;
foreach (var item in objOpeningStock.OpeningStockItem)
{
if (item.ItemId == null || item.ItemId == 0)
{
continue;
}
string sql = @"insert into StockUpdate(StockPointId,stocktrnDate,ItemId,Quantity,
StockType,StockInOut,CreatedBy,CreatedDate,OrganizationId)
Values (@stockpointId,@CreatedDate,@ItemId,@Quantity,'OpeningStock','IN',
@CreatedBy,@CreatedDate,@OrganizationId);
SELECT CAST(SCOPE_IDENTITY() as int)";
//id = connection.Query<int>(sql, objOpeningStock).Single();
id = connection.Query <int>(sql, new
{
stockpointId = objOpeningStock.stockpointId,
ItemId = item.ItemId,
Quantity = item.Quantity,
CreatedBy = objOpeningStock.CreatedBy,
CreatedDate = objOpeningStock.CreatedDate,
OrganizationId = objOpeningStock.OrganizationId
}, txn).Single();
}
return(id);
//}
}
private async Task <bool> ImportEntryData(string fileType, List <CSVDataSummary> eslst, int docSetId, bool overWriteExisting)
{
try
{
var ed = (from es in eslst
group es by new { es.EntryDataId, es.EntryDataDate, es.CustomerName, es.SupplierCode }
into g
let supplier = EntryDataDS.DataModels.BaseDataModel.Instance.SearchSuppliers(new List <string>()
{
string.Format("SupplierCode == \"{0}\"", g.Key.SupplierCode)
})
//where supplier != null
select new
{
e =
new
{
EntryDataId = g.Key.EntryDataId,
EntryDataDate = g.Key.EntryDataDate,
AsycudaDocumentSetId = docSetId,
CustomerName = g.Key.CustomerName,
Tax = g.Sum(x => x.Tax),
Supplier = supplier
},
d = g.Select(x => new EntryDataDetails()
{
EntryDataId = x.EntryDataId,
ItemNumber = x.ItemNumber.ToUpper(),
ItemDescription = x.ItemDescription,
Cost = Convert.ToDouble(x.Cost),
Quantity = Convert.ToDouble(x.Quantity),
Units = x.Units,
Freight = x.Freight,
Weight = x.Weight,
InternalFreight = x.InternalFreight
}),
f = g.Select(x => new
{
TotalWeight = x.TotalWeight,
TotalFreight = x.TotalFreight,
TotalInternalFreight = x.TotalInternalFreight
})
}).ToList();
if (ed == null)
{
return(true);
}
List <EntryData> eLst = null;
foreach (var item in ed)
{
// check Existing items
var olded = await GetEntryData(item.e.EntryDataId, item.e.EntryDataDate).ConfigureAwait(false);
if (olded != null)
{
switch (overWriteExisting)
{
case true:
await ClearEntryDataDetails(olded).ConfigureAwait(false);
await DeleteEntryData(olded).ConfigureAwait(false);
break;
case false:
continue;
}
}
switch (fileType)
{
case "Sales":
var EDsale = new Sales(true)
{
EntryDataId = item.e.EntryDataId,
EntryDataDate = item.e.EntryDataDate,
INVNumber = item.e.EntryDataId,
TaxAmount = item.e.Tax,
CustomerName = item.e.CustomerName,
TrackingState = TrackingState.Added
};
EDsale.AsycudaDocumentSets.Add(new AsycudaDocumentSetEntryData(true)
{
AsycudaDocumentSetId = item.e.AsycudaDocumentSetId,
EntryDataId = item.e.EntryDataId,
TrackingState = TrackingState.Added
});
await CreateSales(EDsale).ConfigureAwait(false);
break;
case "PO":
var EDpo = new PurchaseOrders(true)
{
EntryDataId = item.e.EntryDataId,
EntryDataDate = item.e.EntryDataDate,
PONumber = item.e.EntryDataId,
TrackingState = TrackingState.Added,
TotalFreight = item.f.Sum(x => x.TotalFreight),
TotalInternalFreight = item.f.Sum(x => x.TotalInternalFreight),
TotalWeight = item.f.Sum(x => x.TotalWeight)
};
EDpo.AsycudaDocumentSets.Add(new AsycudaDocumentSetEntryData(true)
{
AsycudaDocumentSetId = item.e.AsycudaDocumentSetId,
EntryDataId = item.e.EntryDataId,
TrackingState = TrackingState.Added
});
await CreatePurchaseOrders(EDpo).ConfigureAwait(false);
break;
case "OPS":
var EDops = new OpeningStock(true)
{
EntryDataId = item.e.EntryDataId,
EntryDataDate = item.e.EntryDataDate,
OPSNumber = item.e.EntryDataId,
TrackingState = TrackingState.Added,
TotalFreight = item.f.Sum(x => x.TotalFreight),
TotalInternalFreight = item.f.Sum(x => x.TotalInternalFreight),
TotalWeight = item.f.Sum(x => x.TotalWeight)
};
EDops.AsycudaDocumentSets.Add(new AsycudaDocumentSetEntryData(true)
{
AsycudaDocumentSetId = item.e.AsycudaDocumentSetId,
EntryDataId = item.e.EntryDataId,
TrackingState = TrackingState.Added
});
await CreateOpeningStock(EDops).ConfigureAwait(false);
break;
default:
throw new ApplicationException("Unknown FileType");
return(true);
}
if (item.d.Count() == 0)
{
throw new ApplicationException(item.e.EntryDataId + " has no details");
}
var details = item.d;
using (var ctx = new EntryDataDetailsService())
{
foreach (var e in details)
{
await ctx.CreateEntryDataDetails(new EntryDataDetails(true)
{
EntryDataId = e.EntryDataId,
ItemNumber = e.ItemNumber,
ItemDescription = e.ItemDescription,
Quantity = e.Quantity,
Cost = e.Cost,
Units = e.Units,
TrackingState = TrackingState.Added,
Freight = e.Freight,
Weight = e.Weight,
InternalFreight = e.InternalFreight,
}).ConfigureAwait(false);
}
}
}
return(false);
}
catch (Exception)
{
throw;
}
}
public HttpResponseMessage Get([FromUri] int Id, [FromBody] int LocationId)
{
return(Request.CreateResponse(HttpStatusCode.OK, OpeningStock.GetDetails(Id, LocationId)));
}
public HttpResponseMessage Get([FromBody] int LocationId, [FromUri] int?SupplierId, [FromUri] DateTime?from, [FromUri] DateTime?to)
{
return(Request.CreateResponse(HttpStatusCode.OK, OpeningStock.GetDetails(LocationId, SupplierId, from, to)));
}
private async Task <bool> ImportEntryData(string fileType, List <CSVDataSummary> eslst, int docSetId, bool overWriteExisting)
{
var ed =
eslst.GroupBy(es => new { es.EntryDataId, es.EntryDataDate, es.CustomerName, es.Tax })
.Select(g => new
{
e =
new
{
EntryDataId = g.Key.EntryDataId,
EntryDataDate = g.Key.EntryDataDate,
AsycudaDocumentSetId = docSetId,
CustomerName = g.Key.CustomerName,
Tax = g.Key.Tax
},
d = g.Select(x => new EntryDataDetails()
{
EntryDataId = x.EntryDataId,
ItemNumber = x.ItemNumber.ToUpper(),
ItemDescription = x.ItemDescription,
Cost = x.Cost,
Quantity = x.Quantity,
Units = x.Units
})
});
if (ed == null)
{
return(true);
}
List <EntryData> eLst = null;
foreach (var item in ed)
{
// check Existing items
var olded = await GetEntryData(item.e.EntryDataId, item.e.EntryDataDate).ConfigureAwait(false);
if (olded != null)
{
switch (overWriteExisting)
{
case true:
await ClearEntryDataDetails(olded).ConfigureAwait(false);
await DeleteEntryData(olded).ConfigureAwait(false);
break;
case false:
continue;
}
}
switch (fileType)
{
case "Sales":
var EDsale = new Sales()
{
EntryDataId = item.e.EntryDataId,
EntryDataDate = item.e.EntryDataDate,
INVNumber = item.e.EntryDataId,
TaxAmount = item.e.Tax,
CustomerName = item.e.CustomerName,
TrackingState = TrackingState.Added
};
EDsale.AsycudaDocumentSets.Add(new AsycudaDocumentSetEntryData()
{
AsycudaDocumentSetId = item.e.AsycudaDocumentSetId,
EntryDataId = item.e.EntryDataId,
TrackingState = TrackingState.Added
});
await CreateSales(EDsale).ConfigureAwait(false);
break;
case "PO":
var EDpo = new PurchaseOrders()
{
EntryDataId = item.e.EntryDataId,
EntryDataDate = item.e.EntryDataDate,
PONumber = item.e.EntryDataId,
TrackingState = TrackingState.Added
};
EDpo.AsycudaDocumentSets.Add(new AsycudaDocumentSetEntryData()
{
AsycudaDocumentSetId = item.e.AsycudaDocumentSetId,
EntryDataId = item.e.EntryDataId,
TrackingState = TrackingState.Added
});
await CreatePurchaseOrders(EDpo).ConfigureAwait(false);
break;
case "OPS":
var EDops = new OpeningStock()
{
EntryDataId = item.e.EntryDataId,
EntryDataDate = item.e.EntryDataDate,
OPSNumber = item.e.EntryDataId,
TrackingState = TrackingState.Added
};
EDops.AsycudaDocumentSets.Add(new AsycudaDocumentSetEntryData()
{
AsycudaDocumentSetId = item.e.AsycudaDocumentSetId,
EntryDataId = item.e.EntryDataId,
TrackingState = TrackingState.Added
});
await CreateOpeningStock(EDops).ConfigureAwait(false);
break;
default:
MessageBox.Show("Unknown FileType");
return(true);
}
if (item.d.Count() == 0)
{
MessageBox.Show(item.e.EntryDataId + " has no details");
}
var details = item.d;
using (var ctx = new EntryDataDetailsService())
{
foreach (var e in details)
{
await ctx.CreateEntryDataDetails(new EntryDataDetails()
{
EntryDataId = e.EntryDataId,
ItemNumber = e.ItemNumber,
ItemDescription = e.ItemDescription,
Quantity = e.Quantity,
Cost = e.Cost,
Units = e.Units,
TrackingState = TrackingState.Added
}).ConfigureAwait(false);
}
}
}
//MessageBus.Default.BeginNotify(MessageToken.EntryDataExChanged, null,
// new NotificationEventArgs(MessageToken.EntryDataExChanged));
//MessageBus.Default.BeginNotify(MessageToken.EntryDataDetailsExesChanged, null,
// new NotificationEventArgs(MessageToken.EntryDataDetailsExesChanged));
return(false);
}
private void CANCELkryptonButton_Click(object sender, EventArgs e)
{
OPENING_STOCK = null;
this.Close();
}
public int SaveUpdateOpenningStock(OpeningStock obOpStk, SqlConnection con, SqlTransaction trans)
{
int ID = 0;
SqlCommand com = null;
try
{
com = new SqlCommand("spSaveOpentingStock", con, trans);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@OpeningStockID", SqlDbType.Int).Value = obOpStk.OpeningID;
//com.Parameters.Add("@FiscalYearID", SqlDbType.Int).Value = DBNull.Value;
com.Parameters.Add("@CustomerID", SqlDbType.Int).Value = obOpStk.CustomerID;
com.Parameters.Add("@ItemID", SqlDbType.Int).Value = obOpStk.ItemID;
com.Parameters.Add("@OpeningQty", SqlDbType.Money).Value = obOpStk.OpeningQuantity;
if (obOpStk.UnitID == 0)
{
com.Parameters.Add("@UnitID", SqlDbType.Int).Value = DBNull.Value;
}
else
{
com.Parameters.Add("@UnitID", SqlDbType.Int).Value = obOpStk.UnitID;
}
com.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = obOpStk.UnitPrice;
com.Parameters.Add("@OpeningAmount", SqlDbType.Money).Value = obOpStk.OpeningAmount;
com.Parameters.Add("@OpeningDate", SqlDbType.DateTime).Value = obOpStk.OpeningDate;
com.Parameters.Add("@CompanyID", SqlDbType.Int).Value = LogInInfo.CompanyID;
com.Parameters.Add("@UserID", SqlDbType.Int).Value = LogInInfo.UserID;
com.Parameters.Add("@DollorRate", SqlDbType.Money).Value = obOpStk.DRate;
com.Parameters.Add("@ColorCode", SqlDbType.VarChar, 100).Value = obOpStk.ColorCode;
com.Parameters.Add("@Labdip", SqlDbType.VarChar, 100).Value = obOpStk.Labdip;
com.Parameters.Add("@Remarks", SqlDbType.VarChar, 500).Value = obOpStk.Remarks;
if (obOpStk.CountID == 0)
{
com.Parameters.Add("@CountID", SqlDbType.Int).Value = DBNull.Value;
}
else
{
com.Parameters.Add("@CountID", SqlDbType.Int).Value = obOpStk.CountID;
}
if (obOpStk.SizeID == 0)
{
com.Parameters.Add("@SizeID", SqlDbType.Int).Value = DBNull.Value;
}
else
{
com.Parameters.Add("@SizeID", SqlDbType.Int).Value = obOpStk.SizeID;
}
if (obOpStk.ColorID == 0)
{
com.Parameters.Add("@ColorID", SqlDbType.Int).Value = DBNull.Value;
}
else
{
com.Parameters.Add("@ColorID", SqlDbType.Int).Value = obOpStk.ColorID;
}
com.Parameters.Add("@CurrencyID", SqlDbType.Int).Value = obOpStk.CurrencyID;
com.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(ID);
}
// public int SaveStockUpdate(OpeningStock model)
// {
// using (IDbConnection connection = OpenConnection(dataConnection))
// {
// int id = 0;
// foreach (var item in model.OpeningStockItem)
// {
// if ( item.OpeningStockId > 0) continue;
// string sql = @"insert into StockUpdate(StockPointId,stocktrnDate,ItemId,Quantity,
// StockType,StockInOut,CreatedBy,CreatedDate,OrganizationId)
// Values (@stockpointId,@CreatedDate,@ItemId,@Quantity,'OpeningStock','IN',
// @CreatedBy,@CreatedDate,@OrganizationId);
// SELECT CAST(SCOPE_IDENTITY() as int)";
// //id = connection.Query<int>(sql, item).Single();
// id = connection.Query<int>(sql, new
// {
// stockpointId = model.stockpointId,
// ItemId = item.ItemId,
// Quantity = item.Quantity,
// CreatedBy = model.CreatedBy,
// CreatedDate = model.CreatedDate,
// OrganizationId = model.OrganizationId
// }).Single();
// }
// return id;
// }
// }
public int SaveOpeningStock(OpeningStock model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
string sql = string.Empty;
foreach (var item in model.OpeningStockItem)
{
if (item.OpeningStockId == 0 && item.Quantity > 0)
{
sql = @"insert into OpeningStock(ItemId,Quantity,
CreatedBy,CreatedDate,OrganizationId,StockpointId)
Values (@ItemId,@Quantity,
@CreatedBy,@CreatedDate,@OrganizationId,@StockpointId);
insert into StockUpdate(StockPointId,stocktrnDate,ItemId,Quantity,
StockType,StockInOut,CreatedBy,CreatedDate,OrganizationId)
Values (@stockpointId,@CreatedDate,@ItemId,@Quantity,'OpeningStock','IN',
@CreatedBy,@CreatedDate,@OrganizationId);
SELECT CAST(SCOPE_IDENTITY() as int)";
int ObjStandardRate = connection.Query <int>(sql, new
{
StockPointId = model.stockpointId,
ItemId = item.ItemId,
Quantity = item.Quantity,
CreatedBy = model.CreatedBy,
CreatedDate = model.CreatedDate,
OrganizationId = model.OrganizationId
}, txn).First();
}
else
{
if (!item.isUsed)
{
sql = @"update OpeningStock set ItemId=@ItemId,Quantity=@Quantity,CreatedBy= @CreatedBy,CreatedDate=@CreatedDate,OrganizationId=@OrganizationId,StockpointId=@StockpointId where OpeningStockId=@OpeningStockId;";
int ObjStandardRate = connection.Execute(sql, new
{
StockPointId = model.stockpointId,
OpeningStockId = item.OpeningStockId,
ItemId = item.ItemId,
Quantity = item.Quantity,
CreatedBy = model.CreatedBy,
CreatedDate = model.CreatedDate,
OrganizationId = model.OrganizationId
}, txn);
sql = @"update StockUpdate set StockPointId=@StockPointId,stocktrnDate=@CreatedDate,ItemId=@ItemId,Quantity=@Quantity,CreatedBy= @CreatedBy,CreatedDate=@CreatedDate,OrganizationId=@OrganizationId where StockType='OpeningStock' and StockInOut='IN' and ItemId=@ItemId and StockpointId=@StockpointId ;";
int ObjReturn = connection.Execute(sql, new
{
StockPointId = model.stockpointId,
OpeningStockId = item.OpeningStockId,
ItemId = item.ItemId,
Quantity = item.Quantity,
CreatedBy = model.CreatedBy,
CreatedDate = model.CreatedDate,
OrganizationId = model.OrganizationId
}, txn);
if (ObjReturn == 0 && item.Quantity > 0)
{
sql = @" insert into StockUpdate(StockPointId,stocktrnDate,ItemId,Quantity,
StockType,StockInOut,CreatedBy,CreatedDate,OrganizationId)
Values (@stockpointId,@CreatedDate,@ItemId,@Quantity,'OpeningStock','IN',
@CreatedBy,@CreatedDate,@OrganizationId);
SELECT CAST(SCOPE_IDENTITY() as int)";
int Objstckup = connection.Query <int>(sql, new
{
StockPointId = model.stockpointId,
ItemId = item.ItemId,
Quantity = item.Quantity,
CreatedBy = model.CreatedBy,
CreatedDate = model.CreatedDate,
OrganizationId = model.OrganizationId
}, txn).First();
}
}
}
}
}
catch (Exception)
{
txn.Rollback();
throw;
}
txn.Commit();
return(1);
}
}
public int UpdateOpeningStock(OpeningStock model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
var list = (from OpeningStockItem p in model.OpeningStockItem
where !p.isUsed
select p).ToList();
var openingStockId = (from OpeningStockItem p in list
where p.OpeningStockId > 0
select p.OpeningStockId).ToList();
IEnumerable <OpeningStockItem> original_list = GetItem(model.stockpointId);
var original_id = (from OpeningStockItem p in original_list
select p.OpeningStockId).ToList();
var excluded_id = (from int p in original_id
where !model.OpeningStockItem.Select(x => x.OpeningStockId).Contains(p)
select p).ToList();
string query = @"DELETE FROM OpeningStock WHERE OpeningStockId IN @openingStockId";
connection.Execute(query, new { openingStockId = openingStockId }, txn);
query = @"DELETE FROM OpeningStock WHERE OpeningStockId IN @excluded_id";
connection.Execute(query, new { excluded_id = excluded_id }, txn);
query = @"INSERT INTO OpeningStock
(
ItemId,
Quantity,
CreatedBy,
CreatedDate,
OrganizationId,
StockPointId
)
VALUES
(
@ItemId,
@Quantity,
@CreatedBy,
@CreatedDate,
@OrganizationId,
@StockPointId
)";
var id = 0;
foreach (var item in list)
{
id = connection.Execute(query, new
{
ItemId = item.ItemId,
Quantity = item.Quantity,
CreatedBy = model.CreatedBy,
CreatedDate = model.CreatedDate,
OrganizationId = model.OrganizationId,
StockpointId = model.stockpointId
}, txn);
}
query = @"DELETE FROM StockUpdate WHERE StockpointId = @StockpointId AND StockType='OpeningStock'";
connection.Execute(query, new { StockpointId = model.stockpointId }, txn);
InsertStockUpdate(model, connection, txn);
InsertLoginHistory(dataConnection, model.CreatedBy, "Create", typeof(OpeningStock).Name, id.ToString(), model.OrganizationId.ToString());
txn.Commit();
return(id);
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
}
}
