예제 #1
0
        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));
        }