public string AddStock(Stock stock)
{
//connection = db.connection;
db = connection.database;
returnString.SuccessMessage = "";
returnString.ErrorMessage = "";
returnString.AppData = "";
CommonController common = new CommonController();
AppConstants appConstants = new AppConstants();
//string query = "insert into stock(item_name,quantity,defective,dead,date,place,challan_no) values('" + stock.ItemName + "'," + stock.Quantity + ",'" + stock.Defective + "','" + stock.Dead + "','" + stock.Date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture) + "','" + stock.Place + "','" + stock.ChallanNumber + "')";
//List<Stock> stockList = new List<Stock>();
try
{
//connection.Open();
//MySqlCommand cmd = new MySqlCommand(query, connection);
//int rowsAffected = cmd.ExecuteNonQuery();
//cmd.Dispose();
//connection.Close();
//var date = JsonConvert.DeserializeObject("{ \"$date\" : "+ stock.Date.ToString() +" }");
var collection = db.GetCollection <Stock>("Stock");
if (collection.CountDocuments(FilterDefinition <Stock> .Empty) == 0)
{
var filter = Builders <Sequence> .Filter.Eq(a => a.Name, "StockID");
var coll = db.GetCollection <Sequence>("Sequence").FindOneAndDelete(filter);
}
stock.StockID = common.GetNextSequenceValue("StockID", db);
if (String.IsNullOrEmpty(stock.ChallanNumber))
{
stock.ChallanNumber = ConfigurationManager.AppSettings["Default_ChallanNumber"];
}
collection.InsertOne(stock);
returnString.SuccessMessage = "Data inserted successfully";
}
catch (Exception ex)
{
returnString.ErrorMessage = ex.Message;
}
return(JsonConvert.SerializeObject(returnString));
}
public string AddEngineer(Engineer engineer)
{
db = connection.database;
returnString.SuccessMessage = "";
returnString.ErrorMessage = "";
returnString.AppData = "";
CommonController common = new CommonController();
try
{
var collection = db.GetCollection <Engineer>("Engineer");
var duplicateFilter = collection.AsQueryable().Where(x => x.EngineerName.ToLower() == engineer.EngineerName.ToLower() && x.EmailID.ToLower() == engineer.EmailID.ToLower() && x.Role == engineer.Role).ToList();
var duplicateEmail = collection.AsQueryable().Where(x => x.EmailID.ToLower() == engineer.EmailID.ToLower()).ToList();
if (duplicateFilter.Count > 0)
{
throw new Exception("Engineer already present in database");
}
if (duplicateEmail.Count > 0)
{
throw new Exception("EmailID already taken by another user");
}
if (collection.CountDocuments(FilterDefinition <Engineer> .Empty) == 0)
{
var filter = Builders <Sequence> .Filter.Eq(a => a.Name, "EngineerID");
var coll = db.GetCollection <Sequence>("Sequence").FindOneAndDelete(filter);
}
engineer.EngineerID = common.GetNextSequenceValue("EngineerID", db);
collection.InsertOne(engineer);
returnString.SuccessMessage = "Data inserted successfully";
}
catch (Exception ex)
{
returnString.ErrorMessage = ex.Message;
}
return(JsonConvert.SerializeObject(returnString));
}
public string AddGroup(Group group)
{
db = connection.database;
returnString.SuccessMessage = "";
returnString.ErrorMessage = "";
returnString.AppData = "";
CommonController common = new CommonController();
try
{
var collection = db.GetCollection <Group>("Group");
var nameFilter = collection.AsQueryable().Where(x => x.GroupName.ToLower() == group.GroupName.ToLower()).ToList();
if (nameFilter.Count > 0)
{
throw new Exception("Group name already exists");
}
if (collection.CountDocuments(FilterDefinition <Group> .Empty) == 0)
{
var filter = Builders <Sequence> .Filter.Eq(a => a.Name, "GroupID");
var coll = db.GetCollection <Sequence>("Sequence").FindOneAndDelete(filter);
}
group.GroupID = common.GetNextSequenceValue("GroupID", db);
collection.InsertOne(group);
returnString.SuccessMessage = "Data inserted successfully";
}
catch (Exception ex)
{
returnString.ErrorMessage = ex.Message;
}
return(JsonConvert.SerializeObject(returnString));
}
public string ReportUsage(Usage usageDetail)
{
db = connection.database;
returnString.SuccessMessage = "";
returnString.ErrorMessage = "";
returnString.AppData = "";
CommonController common = new CommonController();
try
{
var resultDateTime = new DateTime();
var allocationCollection = db.GetCollection <AllocationDetail>("AllocationDetail");
var collection = db.GetCollection <Usage>("Usage");
//usageDetail.UsageDate = new DateTime(usageDetail.UsageDate.Year, usageDetail.UsageDate.Month, usageDetail.UsageDate.Day, 0,0,0,0,DateTimeKind.Utc);
var result = allocationCollection.AsQueryable().Where(x => x.Engineer == usageDetail.Engineer && x.Item == usageDetail.Item).OrderBy(x => x.AllocationDate).ToList();
var resultWithDateFilter = allocationCollection.AsQueryable().Where(x => x.Engineer == usageDetail.Engineer && x.Item == usageDetail.Item && usageDetail.UsageDate.AddDays(1) > x.AllocationDate).OrderBy(x => x.AllocationDate).ToList();
if (resultWithDateFilter.Count != 0)
{
resultDateTime = new DateTime(resultWithDateFilter.FirstOrDefault().AllocationDate.Year, resultWithDateFilter.FirstOrDefault().AllocationDate.Month, resultWithDateFilter.FirstOrDefault().AllocationDate.Day, 0, 0, 0, 0, DateTimeKind.Utc);
}
int totalQuantityAllocated = resultWithDateFilter.AsQueryable().Sum(x => x.QuantityAllocated);
if (result.Count() == 0)
{
returnString.ErrorMessage = usageDetail.Item.ItemName + " is not allocated to " + usageDetail.Engineer.EngineerName;
}
else if (usageDetail.UsageDate < resultDateTime)
{
returnString.ErrorMessage = usageDetail.Item.ItemName + " cannot be reported by " + usageDetail.Engineer.EngineerName + " for selected date. Please check allocation date";
}
else if (usageDetail.QuantityUsed > totalQuantityAllocated)
{
returnString.ErrorMessage = usageDetail.Engineer.EngineerName + " can report only " + totalQuantityAllocated + " " + usageDetail.Item.ItemName + "/s for selected date.";
}
//else if(collection.Find(x => x.BillNumber == usageDetail.BillNumber).Any())
//{
// returnString.ErrorMessage = "Bill number " + usageDetail.BillNumber + " already exists";
//}
else
{
if (collection.CountDocuments(FilterDefinition <Usage> .Empty) == 0)
{
var filter = Builders <Sequence> .Filter.Eq(a => a.Name, "UsageID");
db.GetCollection <Sequence>("Sequence").FindOneAndDelete(filter);
}
usageDetail.UsageID = common.GetNextSequenceValue("UsageID", db);
collection.InsertOne(usageDetail);
returnString.SuccessMessage = "Data inserted successfully";
while (usageDetail.QuantityUsed > 0)
{
var usedItem = allocationCollection.AsQueryable().Where(x => x.Engineer == usageDetail.Engineer && x.Item == usageDetail.Item).OrderBy(x => x.AllocationDate).FirstOrDefault();
var quantity = usedItem.QuantityAllocated;
var setQuantity = quantity - usageDetail.QuantityUsed;
if (setQuantity > 0)
{
var allocationFilter = Builders <AllocationDetail> .Filter.Eq(x => x.AllocationID, usedItem.AllocationID);
var update = Builders <AllocationDetail> .Update.Set(x => x.QuantityAllocated, setQuantity);
var updatedDocument = allocationCollection.FindOneAndUpdate(allocationFilter, update);
usageDetail.QuantityUsed = 0;
}
else
{
if (setQuantity < 0)
{
usageDetail.QuantityUsed = usageDetail.QuantityUsed - quantity;
}
else if (setQuantity == 0)
{
usageDetail.QuantityUsed = 0;
}
allocationCollection.DeleteOne <AllocationDetail>(x => x.AllocationID == usedItem.AllocationID);
}
}
}
}
catch (Exception ex)
{
returnString.ErrorMessage = ex.Message;
}
return(JsonConvert.SerializeObject(returnString));
}
public string FixDefect(AllocationDetail allocation)
{
//connection = db.connection;
db = connection.database;
returnString.SuccessMessage = "";
returnString.ErrorMessage = "";
returnString.AppData = "";
CommonController common = new CommonController();
try
{
var collection = db.GetCollection <Stock>("Stock");
var allocationCollection = db.GetCollection <AllocationDetail>("AllocationDetail");
if (collection.CountDocuments(FilterDefinition <Stock> .Empty) == 0)
{
var filter = Builders <Sequence> .Filter.Eq(a => a.Name, "StockID");
var coll = db.GetCollection <Sequence>("Sequence").FindOneAndDelete(filter);
}
Stock stock = new Stock();
stock.StockID = common.GetNextSequenceValue("StockID", db);
stock.City = allocation.City;
stock.Date = DateTime.Now;
stock.Dead = 0;
stock.Defective = 0;
stock.Quantity = allocation.QuantityAllocated;
stock.Item = allocation.Item;
if (String.IsNullOrEmpty(stock.ChallanNumber))
{
stock.ChallanNumber = ConfigurationManager.AppSettings["Default_ChallanNumber"];
}
collection.InsertOne(stock);
returnString.SuccessMessage = "Data inserted successfully";
while (allocation.QuantityAllocated > 0)
{
var usedItem = allocationCollection.AsQueryable().Where(x => x.AllocationID == allocation.AllocationID).FirstOrDefault();
var quantity = usedItem.QuantityAllocated;
var setQuantity = quantity - allocation.QuantityAllocated;
if (setQuantity > 0)
{
var allocationFilter = Builders <AllocationDetail> .Filter.Eq(x => x.AllocationID, usedItem.AllocationID);
var update = Builders <AllocationDetail> .Update.Set(x => x.QuantityAllocated, setQuantity);
var updatedDocument = allocationCollection.FindOneAndUpdate(allocationFilter, update);
allocation.QuantityAllocated = 0;
}
else
{
if (setQuantity < 0)
{
allocation.QuantityAllocated = allocation.QuantityAllocated - quantity;
}
else if (setQuantity == 0)
{
allocation.QuantityAllocated = 0;
}
allocationCollection.DeleteOne <AllocationDetail>(x => x.AllocationID == usedItem.AllocationID);
}
}
}
catch (Exception ex)
{
returnString.ErrorMessage = ex.Message;
}
return(JsonConvert.SerializeObject(returnString));
}
public string AllocateItem(AllocationDetail allocationDetail)
{
db = connection.database;
returnString.SuccessMessage = "";
returnString.ErrorMessage = "";
returnString.AppData = "";
CommonController common = new CommonController();
AppConstants appConstants = new AppConstants();
try
{
var resultDateTime = new DateTime();
var stockCollection = db.GetCollection <Stock>("Stock");
var collection = db.GetCollection <AllocationDetail>("AllocationDetail");
//allocationDetail.AllocationDate = new DateTime(allocationDetail.AllocationDate.Year, allocationDetail.AllocationDate.Month, allocationDetail.AllocationDate.Day, 0, 0, 0, 0, DateTimeKind.Utc);
var allocationResult = collection.Find(x => x.Item == allocationDetail.Item && x.Engineer == allocationDetail.Engineer && x.AllocationDate >= allocationDetail.AllocationDate.Date && x.AllocationDate < allocationDetail.AllocationDate.Date.AddDays(1)).ToList();
var result = stockCollection.AsQueryable().Where(x => x.Item == allocationDetail.Item && x.City == allocationDetail.City).OrderBy(x => x.Date).ToList();//.Select(n => new { Item=n.Item, Quantity=n.Quantity, Defective=n.Defective, Dead=n.Dead, ChallanNumber=n.ChallanNumber, Date=n.Date, Place=n.Place, assignableQuantity = n.Quantity - n.Defective - n.Dead });
var resultWithDateFilter = stockCollection.AsQueryable().Where(x => x.Item == allocationDetail.Item && x.City == allocationDetail.City && allocationDetail.AllocationDate.AddDays(1) > x.Date).OrderBy(x => x.Date).ToList();
if (resultWithDateFilter.Count != 0)
{
resultDateTime = new DateTime(resultWithDateFilter.FirstOrDefault().Date.Year, resultWithDateFilter.FirstOrDefault().Date.Month, resultWithDateFilter.FirstOrDefault().Date.Day, 0, 0, 0, 0, DateTimeKind.Utc);
}
int totalQuantity = resultWithDateFilter.Sum(x => x.Quantity);
int totalDefective = resultWithDateFilter.Sum(x => x.Defective);
int totalDead = resultWithDateFilter.Sum(x => x.Dead);
if (result.Count() == 0)
{
returnString.ErrorMessage = allocationDetail.Item.ItemName + " is not in " + allocationDetail.City.CityName + " stock";
}
else if (allocationDetail.AllocationDate < resultDateTime)
{
returnString.ErrorMessage = "Allocation of " + allocationDetail.Item.ItemName + " cannot be done on selected date. Please check the date when item is added to " + allocationDetail.City.CityName + " stock";
}
else if (allocationDetail.Engineer != null && allocationDetail.QuantityAllocated > totalQuantity - totalDefective - totalDead)
{
returnString.ErrorMessage = "Insufficient quantity of " + allocationDetail.Item.ItemName + "s which can be allocated. Please check the stock";
}
else
{
if (allocationResult.Any())
{
var filter = Builders <AllocationDetail> .Filter.Eq(x => x.AllocationID, allocationResult.FirstOrDefault().AllocationID);
var update = Builders <AllocationDetail> .Update.Set(x => x.QuantityAllocated, allocationResult.FirstOrDefault().QuantityAllocated + allocationDetail.QuantityAllocated);
var updatedDocument = collection.FindOneAndUpdate(filter, update);
}
else
{
if (collection.CountDocuments(FilterDefinition <AllocationDetail> .Empty) == 0)
{
var filter = Builders <Sequence> .Filter.Eq(a => a.Name, "AllocationID");
var coll = db.GetCollection <Sequence>("Sequence").FindOneAndDelete(filter);
}
allocationDetail.AllocationID = common.GetNextSequenceValue("AllocationID", db);
collection.InsertOne(allocationDetail);
}
returnString.SuccessMessage = "Data inserted successfully";
while (allocationDetail.QuantityAllocated > 0)
{
var query = allocationDetail.Engineer != null?stockCollection.AsQueryable().Where(x => x.Item == allocationDetail.Item && x.City == allocationDetail.City).Select(n => new { StockID = n.StockID, Item = n.Item, Quantity = n.Quantity, Defective = n.Defective, Dead = n.Dead, ChallanNumber = n.ChallanNumber, Date = n.Date, City = n.City, assignableQuantity = n.Quantity - n.Defective - n.Dead }).ToList() : stockCollection.AsQueryable().Where(x => x.Item == allocationDetail.Item && x.City == allocationDetail.City && x.ChallanNumber == ConfigurationManager.AppSettings["Defective_ChallanNumber"]).Select(n => new { StockID = n.StockID, Item = n.Item, Quantity = n.Quantity, Defective = n.Defective, Dead = n.Dead, ChallanNumber = n.ChallanNumber, Date = n.Date, City = n.City, assignableQuantity = n.Defective }).ToList();
var allocatedItem = query.Where(x => x.assignableQuantity > 0).OrderBy(x => x.Date).FirstOrDefault();
var quantity = allocatedItem.Quantity;
var defective = allocatedItem.Defective;
var dead = allocatedItem.Dead;
var stockId = allocatedItem.StockID;
var setQuantity = quantity - defective - dead - allocationDetail.QuantityAllocated;
if (setQuantity > 0)
{
var stockFilter = Builders <Stock> .Filter.Eq(x => x.StockID, stockId);
var update = Builders <Stock> .Update.Set(x => x.Quantity, quantity - allocationDetail.QuantityAllocated);
var updatedDocument = stockCollection.FindOneAndUpdate(stockFilter, update);
allocationDetail.QuantityAllocated = 0;
}
else
{
if (setQuantity == 0 || allocationDetail.Engineer == null)
{
allocationDetail.QuantityAllocated = 0;
}
else if (setQuantity < 0 && allocationDetail.Engineer != null)
{
allocationDetail.QuantityAllocated = allocationDetail.QuantityAllocated - (quantity - defective - dead);
}
if ((defective == 0 && dead == 0) || (allocationDetail.Engineer == null))
{
stockCollection.DeleteOne <Stock>(x => x.StockID == stockId);
}
else if (defective > 0 || dead > 0)
{
var stockFilter = Builders <Stock> .Filter.Eq(x => x.StockID, stockId);
var update = Builders <Stock> .Update.Set(x => x.Quantity, defective + dead);
var updatedDocument = stockCollection.FindOneAndUpdate(stockFilter, update);
}
}
}
}
}
catch (Exception ex)
{
returnString.ErrorMessage = ex.Message;
}
return(JsonConvert.SerializeObject(returnString));
}