public static List <RequisitionDetails> GetRequisitionsByStationeryCategory()
{
List <RequisitionDetails> reqs = new List <RequisitionDetails>();
using (SqlConnection conn = new SqlConnection(Data.db_cfg))
{
conn.Open();
string q = @"SELECT MONTH(dateOfRequest) as monthOfRequest, YEAR(dateofRequest) as yearOfRequest, SUM(quantity) as total, category from Requisition r, RequisitionDetails rd, Inventory i WHERE r.reqId = rd.reqId AND status = 'Completed' AND rd.itemId = i.itemId GROUP BY MONTH(dateOfRequest),YEAR(dateOfRequest),category";
SqlCommand cmd = new SqlCommand(q, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
RequisitionDetails req = new RequisitionDetails()
{
MonthOfRequest = (int)reader["monthOfRequest"],
YearOfRequest = (int)reader["yearOfRequest"],
Quantity = (int)reader["total"],
Category = (string)reader["category"]
};
reqs.Add(req);
}
return(reqs);
}
}
public static List <RequisitionDetails> GetRequisitionDetailsByReqId(long reqId)
{
List <RequisitionDetails> reqDetails = new List <RequisitionDetails>();
using (SqlConnection conn = new SqlConnection(Data.db_cfg))
{
conn.Open();
string q = @"SELECT * from RequisitionDetails where reqId=" + reqId;
SqlCommand cmd = new SqlCommand(q, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Inventory i = new Inventory()
{
ItemId = (long)reader["itemId"]
};
RequisitionDetails requisitionDetail = new RequisitionDetails()
{
Quantity = (int)reader["quantity"],
Balance = (int)reader["balance"],
Item = i
};
reqDetails.Add(requisitionDetail);
}
}
return(reqDetails);
}
public List <RequisitionDetails> ShowDisbursementDetailDataInfo(int id)
{
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
string getItems = @" select dd.DeliveredQty, s.ItemName, dd.ActualQty, d.Departmentname, dd.DisbursementID, dd.ItemID
from DisbursementDetails dd, Department d, Stationery s
where d.DepartmentID= dd.DepID and dd.ItemID = s.ItemID and dd.DisbursementID= '" + id + "'";
SqlCommand cmd = new SqlCommand(getItems, connection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
RequisitionDetails rd = new RequisitionDetails();
rd.DeliveredQty = (int)reader["DeliveredQty"];
rd.ActualQty = (int)reader["ActualQty"];
rd.ItemName = (string)reader["ItemName"];
rd.DisbursementID = (int)reader["DisbursementID"];
rd.ItemID = (string)reader["ItemID"];
rd.DisbursementStatus = (string)getDisbursementListStatus(rd);
rlItemList.Add(rd);
}
}
return(rlItemList);
}
public static void CreateRequisition(List <Cart> carts, long empId)
{
List <long> reqs = RequisitionDAO.GetAllRequisitions();
Employee emp = new Employee();
emp.EmpId = empId;
Requisition req = new Requisition();
long arg = reqs.Count == 0 ? 0 : reqs.Max();
req.ReqCode = string.Format(String.Format("#R{0:0000000000}", arg + 1));
req.DateOfRequest = DateTime.Now;
req.Status = "Pending Approval";
req.Employee = emp;
long reqId = RequisitionDAO.SaveRequisition(req);
req.ReqId = reqId;
List <RequisitionDetails> reqDetailsList = new List <RequisitionDetails>();
Inventory item = null;
foreach (Cart c in carts)
{
item = new Inventory();
item.ItemId = c.Item.ItemId;
RequisitionDetails reqDetail = new RequisitionDetails();
reqDetail.Requisition = req;
reqDetail.Item = item;
reqDetail.Quantity = c.Quantity;
reqDetail.Balance = c.Quantity;
reqDetailsList.Add(reqDetail);
}
RequisitionDetailsDAO.SaveRequisitionDetails(reqDetailsList);
CartDAO.DeleteCarts(empId);
}
public RequisitionDetails getDataForDiscrepancy(int id, string itemID)
{
RequisitionDetails rDetail = new RequisitionDetails();
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
//wrong
string getItems = @" select dd.DisbursementID, dd.ItemID, s.ItemName, dd.ActualQty, dd.DeliveredQty
from DisbursementDetails dd, Stationery s
where s.ItemID= dd.ItemID and DisbursementID='" + id + "' and dd.ItemID = '" + itemID + "'";
SqlCommand cmd = new SqlCommand(getItems, connection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
rDetail.DisbursementID = (int)reader["DisbursementID"];
rDetail.ItemID = (string)reader["ItemID"];
rDetail.ItemName = (string)reader["ItemName"];
rDetail.ActualQty = (int)reader["ActualQty"];
rDetail.DeliveredQty = (int)reader["DeliveredQty"];
}
}
return(rDetail);
}
public List <RequisitionDetails> ShowDisbursementDetailUserInfo(int id)
{
string depID = getDepartmentByDisbID(id);
int OTP = getOTP(id);
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
string getItems = @"select u.MobileNo, u.Username, d.Departmentname, d.CollectionPoint, u.EmailID
from Users u, Department d
where u.DeptID_FK = d.DepartmentID and
u.role='DepRep' and d.DepartmentID = '" + depID + "'";
SqlCommand cmd = new SqlCommand(getItems, connection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
RequisitionDetails rd = new RequisitionDetails();
rd.MobileNo = (string)reader["MobileNo"];
rd.UserName = (string)reader["Username"];
rd.Departmentname = (string)reader["Departmentname"];
rd.CollectionPoint = (string)reader["CollectionPoint"];
rd.EmailID = (string)reader["EmailID"];
rd.DisbursementID = id;
rd.OTP = OTP;
rlList.Add(rd);
}
}
return(rlList);
}
public List <RequisitionDetails> GetRequisitionDetails(int RequisitionID)
{
List <RequisitionDetails> requisitions = new List <RequisitionDetails>();
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
string cmdquery = @"select s.ItemName,rd.RequisitionQuantity
from RequisitionDetail rd,RequisitionList rl,Stationery s
where rd.RequisitionID=rl.RequisitionID
and rd.ItemID=s.ItemID and rd.RequisitionID=" + RequisitionID;
SqlCommand cmd = new SqlCommand(cmdquery, connection);
//Call Execute reader to get query results
SqlDataReader reader = cmd.ExecuteReader();
//Print out each record
while (reader.Read())
{
RequisitionDetails s = new RequisitionDetails();
s.ItemName = (string)reader["ItemName"];
s.RequisitionQuantity = (int)reader["RequisitionQuantity"];
requisitions.Add(s);
}
return(requisitions);
}
}
public static List <RequisitionDetails> GetRequisitionsByDept(string department)
{
List <RequisitionDetails> reqs = new List <RequisitionDetails>();
using (SqlConnection conn = new SqlConnection(Data.db_cfg))
{
conn.Open();
string sub1 = @"SELECT empId from Employee e, Department d WHERE e.deptId = d.deptId AND deptName = '" + department + "') GROUP BY MONTH(dateOfRequest),YEAR(dateOfRequest)";
string q = @"SELECT MONTH(dateOfRequest) as monthOfRequest, YEAR(dateOfRequest) as yearOfRequest, SUM(quantity) as total from Requisition r, RequisitionDetails rd WHERE r.reqId = rd.reqId AND status = 'Completed' AND empId in (" + sub1;
SqlCommand cmd = new SqlCommand(q, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
RequisitionDetails req = new RequisitionDetails()
{
MonthOfRequest = (int)reader["monthOfRequest"],
YearOfRequest = (int)reader["yearOfRequest"],
Quantity = (int)reader["total"],
};
reqs.Add(req);
}
return(reqs);
}
}
public ActionResult SubmitDiscrepency(RequisitionDetails Dp)
{
String ItemID = Dp.ItemID;
string StockcardID = getstockcardID(ItemID);
string DisStatus = "PendingForApproval";
using (SqlConnection conn = new SqlConnection(DataLink.connectionString))
{
conn.Open();
string cmdtext = @"insert into Discrepancy (DisbursementID,StockCardID,DiscrepancyQty,Reason,DiscrepancyStatus,ItemID) values ('" + Dp.DisbursementID + "','" + StockcardID + "'," + Dp.DeliveredQty + ",'" + Dp.Reason + "','" + DisStatus + "','" + ItemID + "')";
SqlCommand cmd = new SqlCommand(cmdtext, conn);
cmd.ExecuteNonQuery();
}
Users u = ds.GetUserInfo((int)Session["UserID"]);
string EmailID = u.EmailID;
int DiscrepancyId = GetDiscrepancyID();
SendEmailNotification send = new SendEmailNotification();
String EmailSubject = "Request Submitted DiscrepancyID#" + DiscrepancyId;
String EmailBody = "<p> Dear KyiPhyu,</p>";
EmailBody += "<p>Your order is ready to deliver with Discrepancy ID" + DiscrepancyId + " for your reference.</p>";
EmailBody += "<p>Thank you<br/>Logic University Staionery Store</p>";
EmailBody += "<p> Please do not reply to this email it is auto-generated.</p>";
send.SendEmailHTML("*****@*****.**", EmailSubject, EmailBody);
return(RedirectToAction("DisbursementList"));
}
public ActionResult RaiseDiscrepency(RequisitionDetails Db)
{
// ViewBag.DiscrepencyForm = Db;
// RequisitionDetails rd = ds.getDataForDiscrepancy(id, itemID);
// ViewBag.rQty = DeliveredQty;
ViewData["Db"] = Db;
return(View());
}
public HttpResponseMessage RaiseDiscrepency(RequisitionDetails Db)
{
// ViewBag.DiscrepencyForm = Db;
// RequisitionDetails rd = ds.getDataForDiscrepancy(id, itemID);
// ViewBag.rQty = DeliveredQty;
return(ControllerContext.Request.CreateResponse(HttpStatusCode.OK, new { model = Db }));
}
public string validateOTP(RequisitionDetails reqID)
{
Random r = new Random();
string OTPgenerated = (r.Next(100000, 999999)).ToString();
addOTPToDB(OTPgenerated, reqID.DisbursementID);
// string response = SendOTP.sendSMS(reqID.MobileNo, messageBody);
return(OTPgenerated);
}
public List <RequisitionDetails> SplitRDString(string RequisitionDetailsString, List <RequisitionDetails> rdList)
{
string[] RequisitionDetailsArray = RequisitionDetailsString.Split(',');
foreach (string Rdstring in RequisitionDetailsArray)
{
if (Rdstring != "")
{
int RdId = int.Parse(Rdstring);
RequisitionDetails Rd = db.RequisitionDetails.FirstOrDefault(s => s.RequisitionDetailsId == RdId);
rdList.Add(Rd);
}
}
return(rdList);
}
public List <RequisitionDetails> splitString(string RequisitionString)
{
string[] PendingRequisitionDetailsList = RequisitionString.Split(',');
List <RequisitionDetails> rdList = new List <RequisitionDetails>();
foreach (string rd in PendingRequisitionDetailsList)
{
if (rd != "")
{
int int_rd = int.Parse(rd);
RequisitionDetails d0 = db.RequisitionDetails.FirstOrDefault(i => i.RequisitionDetailsId == int_rd);
rdList.Add(d0);
}
}
return(rdList);
}
public static bool CartSubmission(String UserName, FormCollection CartItems)
{
string item_code = null;
string item_qty = null;
var username = UserName;
Employee obj = DepartmentRequestService.GetUser(UserName);
Requisition request = new Requisition();
request.Date = DateTime.Now;
request.Status = "PENDING";
request.EmployeeId = obj.EmployeeId;
request.ApproverId = obj.ApproverId;
request.DeptId = obj.DeptId;
db.Requisition.Add(request);
db.SaveChanges();
EmailService.SendNotification(obj.ApproverId, "Waiting for Approval", "You have received an requisition. Waiting for Approval");
RequisitionDetails requestDetails = new RequisitionDetails();
foreach (var key in CartItems.AllKeys)
{
item_code = CartItems["ItemCode"];
item_qty = CartItems["Quantity"];
}
String[] item_code_s = item_code.Split(',');
String[] item_qty_s = item_qty.Split(',');
for (int i = 0; i < item_code_s.Length; i++)
{
if (Int32.Parse(item_qty_s[i]) != 0)
{
requestDetails.RequisitionId = request.RequisitionId;
requestDetails.ItemCode = item_code_s[i];
requestDetails.Quantity = Int32.Parse(item_qty_s[i]);
db.RequisitionDetails.Add(requestDetails);
db.SaveChanges();
}
}
db.CartItems.RemoveRange(db.CartItems.Where(x => x.EmployeeId == obj.EmployeeId));
db.SaveChanges();
return(true);
}
public ActionResult validateOTP(RequisitionDetails rd)
{
string OTP = ds.validateOTP(rd);
RequisitionDetails userData = ds.getUserDataByDisbID(rd.DisbursementID);
SendEmailNotification send = new SendEmailNotification();
String EmailSubject = "Request Submitted DisbursementID#" + userData.DisbursementID;
String EmailBody = "<p> Dear " + userData.UserName + ",</p>";
EmailBody += "<p>Your order is ready to deliver with Disbursement ID <b>" + userData.DisbursementID + "</b> for your reference.</p>";
EmailBody += "<p>Collection Point will be <b>" + userData.CollectionPoint + "</b></p>";
EmailBody += "<p>" + "OTP for loging into your account is here. <b> " + OTP + " </b>." +
" Please tell this OTP to verify your identity. " + " </p>";
EmailBody += "<p>Thank you<br/>Logic University Staionery Store</p>";
EmailBody += "<p> Please do not reply to this email it is auto-generated.</p>";
send.SendEmailHTML(userData.EmailID, EmailSubject, EmailBody);
int id = rd.DisbursementID;
return(RedirectToAction("ViewDisbursementDetail/" + id));
}
public string getDisbursementListStatus(RequisitionDetails rd)
{
string disListStatus = null;
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
string cmdquery = @"select dl.DisbursementStatus
from DisbursementList dl
where dl.DisbursementID ='" + rd.DisbursementID + "'";
SqlCommand cmd = new SqlCommand(cmdquery, connection);
//Call Execute reader to get query results
SqlDataReader reader = cmd.ExecuteReader();
//System.Guid a = System.Guid.NewGuid();
//Print out each record
while (reader.Read())
{
disListStatus = (string)reader["DisbursementStatus"];
}
return(disListStatus);
}
}
public ActionResult Create(FormCollection form, string sessionId)
{
sessionId = Request["sessionId"];
if (Sessions.IsValidSession(sessionId))
{
ViewData["sessionId"] = sessionId;
int count = int.Parse(Request.Form["count"]);
string DeptString = "";
List <Department> dStringList = new List <Department>();
StockAdjustmentVoucher s = PrepareVoucher();
Retrieval r = PrepareRetrieval();
List <RetrievalDetail> rdList = new List <RetrievalDetail>();
List <StockAdjustmentVoucherDetail> sList = new List <StockAdjustmentVoucherDetail>();
for (int i = 0; i < count; i++)
{
string itemdesc = Request.Form["ICR[" + i + "].product"];
Products p = db.Products.FirstOrDefault(o => o.Description == itemdesc);
string itemcode = p.ItemCode;
int retrievedqty = int.Parse(Request.Form["ICR[" + i + "].retrieved"]);
int qtyininventory = int.Parse(Request.Form["ICR[" + i + "].qtyininventory"]);
int TotalNeeded = int.Parse(Request.Form["ICR[" + i + "].TotalNeeded"]);
string dept = Request.Form["ICR[" + i + "].Dept"];
List <Department> dList = splitString(dept);
foreach (Department d in dList)
{
if (!dStringList.Any(d1 => d1 == d))
{
dStringList.Add(d);
}
}
List <RequisitionDetails> requisitiondetaillist = new List <RequisitionDetails>();
List <RequisitionDetails> requisitiondetaillist1 = db.RequisitionDetails.Where(a => a.ItemCode == itemcode).Where(b => b.Status != "Retrieved").Include(c => c.Requisition).ToList();
requisitiondetaillist = RetrieveRequisitionDetailsByDepartment(requisitiondetaillist, dList, requisitiondetaillist1);
requisitiondetaillist = IncludeSaveAllRequisitionDetails(requisitiondetaillist);
if (TotalNeeded != retrievedqty)
{
sList = AddVoucherDetailToVoucherDetailList(sList, itemcode, retrievedqty - TotalNeeded, null);
requisitiondetaillist = requisitiondetaillist.Where(q => q.ItemCode == itemcode).OrderBy(w => w.Requisition.Date).ToList();
for (int j = 0; j < requisitiondetaillist.Count(); j++)
{
if (retrievedqty >= requisitiondetaillist[j].Quantity)
{
retrievedqty = retrievedqty - requisitiondetaillist[j].Quantity;
}
else
{
int newquantity = requisitiondetaillist[j].Quantity - retrievedqty;
requisitiondetaillist[j].Quantity = retrievedqty;
RequisitionDetails newRD = new RequisitionDetails();
newRD.Quantity = newquantity;
newRD.ItemCode = itemcode;
newRD.RequisitionId = requisitiondetaillist[j].RequisitionId;
db.Entry(newRD).State = EntityState.Added;
db.Entry(requisitiondetaillist[j]).State = EntityState.Modified;
db.SaveChanges();
}
}
if (!db.StockAdjustmentVouchers.Any(s1 => s1.Id.Contains(s.Id)))
{
db.Entry(s).State = EntityState.Added;
db.SaveChanges();
}
rdList = AddRetrievalDetailToRdList(rdList, p.ItemCode, retrievedqty, TotalNeeded, s.Id);
}
else
{
rdList = AddRetrievalDetailToRdList(rdList, p.ItemCode, retrievedqty, TotalNeeded, null);
}
r = CreateRequisitionString(r, requisitiondetaillist);
}
s.StockAdjustmentVoucherDetails = sList;
CheckRequisitionComplete();
foreach (Department dString in dStringList)
{
DeptString = DeptString + "," + dString.DeptName;
}
if (sList.Count() != 0)
{
r = SaveRetrieval(r, rdList);
ViewData["s"] = s;
ViewData["count"] = sList.Count();
ViewData["RequisitionDetailsString"] = r.RequisitionString;
ViewData["DeptString"] = DeptString;
return(View("AdjustRetrieval", s));
//TempData["s"] = s;
//return RedirectToAction("AdjustRetrieval", "Retrievals",new { sessionId = sessionId });
}
else
{
r = SaveRetrieval(r, rdList);
string RequisitionDetailsString = r.RequisitionString;
return(RedirectToAction("DisplayDisbursement", "Disbursements", new { RequisitionDetailsString = RequisitionDetailsString, DeptString = DeptString, sessionId = sessionId }));
}
}
else
{
return(RedirectToAction("Login", "Login"));
}
}