public List <StockReceipt> ConvertDBToList()
{
try
{
OpenConnection();
string queryString = "select * from StockReceipt";
SqlCommand command = new SqlCommand(queryString, conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
List <StockReceipt> stockReceiptList = new List <StockReceipt>();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
int idAccount = -1;
if (dataTable.Rows[i].ItemArray[1].ToString() != "")
{
idAccount = int.Parse(dataTable.Rows[i].ItemArray[1].ToString());
}
StockReceipt acc = new StockReceipt(int.Parse(dataTable.Rows[i].ItemArray[0].ToString()), idAccount,
DateTime.Parse(dataTable.Rows[i].ItemArray[2].ToString()), long.Parse(dataTable.Rows[i].ItemArray[3].ToString()));
stockReceiptList.Add(acc);
}
return(stockReceiptList);
}
catch
{
return(new List <StockReceipt>());
}
finally
{
CloseConnection();
}
}
public bool AddIntoDB(StockReceipt stockReceipt)
{
try
{
OpenConnection();
string queryString = "insert into StockReceipt(idStockReceipt, idAccount, dateTimeStockReceipt, total) " +
"values(@idStockReceipt, @idAccount, @dateTimeStockReceipt, @total)";
SqlCommand command = new SqlCommand(queryString, conn);
command.Parameters.AddWithValue("@idStockReceipt", stockReceipt.IdStockReceipt.ToString());
command.Parameters.AddWithValue("@idAccount", stockReceipt.IdAccount.ToString());
command.Parameters.AddWithValue("@dateTimeStockReceipt", stockReceipt.DateTimeStockReceipt);
command.Parameters.AddWithValue("@total", stockReceipt.Total.ToString());
int rs = command.ExecuteNonQuery();
if (rs != 1)
{
throw new Exception();
}
else
{
return(true);
}
}
catch
{
return(false);
}
finally
{
CloseConnection();
}
}
public bool Insert(StockReceipt stockReceipt)
{
try
{
OpenConnection();
string query = "insert into StockReceipt(idStockReceipt, idAccount, receiptDate, totalPaidMoney, discount, idSupplier) "
+ "values(@idStockReceipt,@idAccount, str_to_date(@receiptDate,'%d/%m/%Y'), @totalPaidMoney, @discount, @idSupplier)";
MySqlCommand cmd = new MySqlCommand(query, conn);
string s = stockReceipt.Date.ToString("dd/MM/yyyy");
cmd.Parameters.AddWithValue("@idStockReceipt", stockReceipt.Id.ToString());
cmd.Parameters.AddWithValue("@idAccount", stockReceipt.IdAccount.ToString());
cmd.Parameters.AddWithValue("@receiptDate", s);
cmd.Parameters.AddWithValue("@totalPaidMoney", stockReceipt.TotalPaidMoney.ToString());
cmd.Parameters.AddWithValue("@discount", stockReceipt.Discount.ToString());
cmd.Parameters.AddWithValue("@idSupplier", stockReceipt.IdSupplier.ToString());
return(cmd.ExecuteNonQuery() == 1);
}
catch
{
return(false);
}
finally
{
CloseConnection();
}
}
public async Task <IActionResult> Add(StockReceipt model)
{
var user = await GetCurrentUserAsync();
var siteId = user.SiteID;
if (ModelState.IsValid)
{
model.SiteId = siteId;
model.ReceivedBy = user.Name;
model.DateReceived = DateTime.Now;
_repository.AddStockReceipt(model);
CreateStockRecord(model);
ModelState.Clear();
return(RedirectToAction("list", new { id = "success" }));
}
//Loads the dropdown data
ViewBag.ProductId = new SelectList(_repository.GetAllProducts(siteId), "ProductId", "ProductCode");
ViewBag.LocationId = new SelectList(_repository.GetAllLocations(siteId), "LocationId", "Name");
return(View(model));
}
public Tuple <bool, string, string> Post(DocumentStockReceiptList stockReceipt = null)
{
ManageDocumentReceipt documentReceipt = new ManageDocumentReceipt();
if (stockReceipt.Type == 2)
{
ManageSQLConnection manageSQLConnection = new ManageSQLConnection();
documentReceipt.GenerateReceipt(stockReceipt);
//update print
if (stockReceipt.UnLoadingSlip == "N" || string.IsNullOrEmpty(stockReceipt.UnLoadingSlip))
{
List <KeyValuePair <string, string> > sqlParameters = new List <KeyValuePair <string, string> >();
sqlParameters.Add(new KeyValuePair <string, string>("@SRNo", stockReceipt.SRNo));
manageSQLConnection.UpdateValues("UpdateSRDetailsUnLoading", sqlParameters);
}
return(new Tuple <bool, string, string>(true, "Print Generated Sucessfully", ""));
}
else
{
ManageSQLConnection manageSQLConnection = new ManageSQLConnection();
List <KeyValuePair <string, string> > sqlParameters = new List <KeyValuePair <string, string> >();
sqlParameters.Add(new KeyValuePair <string, string>("@GCode", stockReceipt.ReceivingCode));
var result = manageSQLConnection.GetDataSetValues("AllowDocumentEntry", sqlParameters);
ManageReport manageReport = new ManageReport();
if (manageReport.CheckDataAvailable(result))
{
if (stockReceipt.SRNo.Trim() != "0" && stockReceipt.SRNo.Trim() != "-")
{
List <KeyValuePair <string, string> > sqlParameters1 = new List <KeyValuePair <string, string> >();
sqlParameters1.Add(new KeyValuePair <string, string>("@Type", "1"));
sqlParameters1.Add(new KeyValuePair <string, string>("@DocNumber", stockReceipt.SRNo.Trim()));
var result1 = manageSQLConnection.GetDataSetValues("CheckDocumentEdit", sqlParameters1);
if (!manageReport.CheckDataAvailable(result1))
{
return(new Tuple <bool, string, string>(false, GlobalVariable.DocumentEditPermission, ""));
}
// CheckDocumentEdit
}
else if (documentReceipt.CheckSRUpdateStatus(stockReceipt.SRNo))
{
return(new Tuple <bool, string, string>(false, GlobalVariable.DocumentEditByHO, ""));
}
StockReceipt receipt = new StockReceipt();
return(receipt.InsertReceiptData(stockReceipt));
}
else
{
return(new Tuple <bool, string, string>(false, "Permission not Granted", ""));
}
}
}
public bool UpdateOnDB(StockReceipt stockReceipt)
{
try
{
OpenConnection();
string queryString = "update StockReceipt set dateTimeStockReceipt=@dateTimeStockReceipt, total=@total " +
"where idStockReceipt =" + stockReceipt.IdStockReceipt.ToString();
SqlCommand command = new SqlCommand(queryString, conn);
command.Parameters.AddWithValue("@dateTimeStockReceipt", stockReceipt.DateTimeStockReceipt);
command.Parameters.AddWithValue("@total", stockReceipt.Total.ToString());
command.ExecuteNonQuery();
return(true);
}
catch
{
return(false);
}
finally
{
CloseConnection();
}
}
private void Import_SaveProduct(ImportProductWindow para)
{
if (string.IsNullOrEmpty(para.txtProductName.Text))
{
para.txtProductName.Focus();
return;
}
if (string.IsNullOrEmpty(para.txtUnit.Text))
{
para.txtUnit.Focus();
return;
}
if (string.IsNullOrEmpty(para.txtImportPrice.Text))
{
para.txtImportPrice.Focus();
return;
}
if (string.IsNullOrEmpty(para.txtAmount.Text))
{
para.txtAmount.Focus();
return;
}
StreamReader sr = new StreamReader("../../cache.txt");
string cache = sr.ReadToEnd();
sr.Close();
string[] rulesSetting = cache.Split(' ');
var results = DataProvider.Instance.DB.Products.Where(x => x.IsDelete == false).Select(x => x.Unit).Distinct().ToList();
if (para.Title == "Thêm sản phẩm" && DataProvider.Instance.DB.Products.Where(x => x.IsDelete == false).ToList().Count >= int.Parse(rulesSetting[2]))
{
MessageBox.Show("Exceed the number of product limit");
return;
}
if (DataProvider.Instance.DB.Products.Where(x => x.IsDelete == false).Where(x => x.Unit == para.txtUnit.Text).ToList().Count < 1)
{
if (results.Count >= int.Parse(rulesSetting[3]))
{
MessageBox.Show("Exceed the number of unit limit");
return;
}
}
string id = para.txtID.Text;
string name = para.txtProductName.Text;
string unit = para.txtUnit.Text;
long importPrice = ConvertToNumber(para.txtImportPrice.Text);
long exportPrice = ConvertToNumber(para.txtStockReceiptID.Text);
int amount = (int)ConvertToNumber(para.txtAmount.Text);
try
{
int productID = int.Parse(para.txtID.Text);
int stockReceiptID = int.Parse(para.txtStockReceiptID.Text);
string productName = para.txtProductName.Text;
string units = para.txtUnit.Text;
importPrice = ConvertToNumber(para.txtImportPrice.Text);
amount = (int)ConvertToNumber(para.txtAmount.Text);
byte[] imgByteArr;
if (imageFileName == null)
{
imgByteArr = Converter.Instance.ConvertImageToBytes(@"..\..\Resources\Images\default.jpg");
}
else
{
imgByteArr = Converter.Instance.ConvertImageToBytes(imageFileName);
}
Product product;
//exits product
if (para.Title == "Import exits product")
{
product = DataProvider.Instance.DB.Products.Where(p => p.ID == productID).First();
product.ID = productID;
product.Name = productName;
product.Unit = units;
product.ImportPrice = importPrice;
product.Count = amount;
product.Image = imgByteArr;
product.IsDelete = false;
}
//import new product
else
{
product = new Product();
product.ID = productID;
product.Name = productName;
product.Unit = units;
product.ImportPrice = importPrice;
product.ExportPrice = 0;
product.Count = amount;
product.Image = imgByteArr;
product.IsDelete = false;
}
StockReceiptInfo stockReceiptInfo = new StockReceiptInfo();
stockReceiptInfo.StockReceiptID = stockReceiptID;
stockReceiptInfo.ProductID = productID;
stockReceiptInfo.Amount = amount;
stockReceiptInfo.Price = importPrice;
StockReceipt stockReceipt = new StockReceipt();
stockReceipt.ID = stockReceiptID;
stockReceipt.CheckIn = DateTime.Now;
stockReceipt.Total = product.Count * product.ImportPrice;
DataProvider.Instance.DB.Products.AddOrUpdate(product);
DataProvider.Instance.DB.StockReceipts.AddOrUpdate(stockReceipt);
DataProvider.Instance.DB.StockReceiptInfoes.AddOrUpdate(stockReceiptInfo);
DataProvider.Instance.DB.SaveChanges();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
this.LoadProduct(this.HomeWindow);
para.Close();
}
}
/// <summary>
/// Creates the stock record on creation of the stock receipt.
/// </summary>
/// <param name="stockReceiptModel"></param>
private void CreateStockRecord(StockReceipt stockReceiptModel)
{
string GRNNo = _repository.GetGRN(stockReceiptModel.SiteId);
//StockItem stockModel = new StockItem { GRNNo = GRNNo, ProductId = stockReceiptModel.ProductId, Product = stockReceiptModel.Product, LocationId= stockReceiptModel.LocationId, Location = stockReceiptModel.Location, SerialNo = stockReceiptModel.SerialNo, BatchNo = stockReceiptModel.BatchNo, DateReceived = stockReceiptModel.DateReceived, ReceivedBy = stockReceiptModel.ReceivedBy, InitialQty = stockReceiptModel.Qty, QtyAvailable = stockReceiptModel.Qty, CostPerUOM = stockReceiptModel.CostPerUOM, TotalCost = stockReceiptModel.TotalCost, Notes=stockReceiptModel.Notes, SiteId = stockReceiptModel.SiteId};
//_repository.AddStockItem(stockModel);
}
