public static void createNewRequisitionList(Dictionary <string, List <RequisitionDetail> > dictionary)
{
UniversityStoreEntities context = new UniversityStoreEntities();
foreach (KeyValuePair <string, List <RequisitionDetail> > kvp in dictionary)
{
DateTime d = DateTime.Now;
Employee emp = context.Employees.First <Employee>(x => x.EmployeeNumber == kvp.Key);
RequisitionList rl = new RequisitionList();
rl.DepartmentCode = emp.DepartmentCode;
rl.EmployeeNumber = emp.EmployeeNumber;
rl.DateCreated = DateTime.Now;
rl.Status = STATUS_PENDING;
EntityCollection <RequisitionDetail> listDetails = new EntityCollection <RequisitionDetail>();
foreach (RequisitionDetail detail in kvp.Value)
{
listDetails.Add(detail);
}
rl.RequisitionDetails = listDetails;
context.AddToRequisitionLists(rl);
context.SaveChanges();
}
}
public List <RequisitionList> getRequisitionListForApproval(string DeptID)
{
List <RequisitionList> Lt_Requisitions = new List <RequisitionList>();
using (SqlConnection C = new SqlConnection(DataLink.connectionString))
{
C.Open();
string cmdtext = @"select u.Username, RL.RequisitionID,Rl.Comments,RL.statusOfRequest,RL.DateofSubmission,RL.UserID_FK,Rl.DeptID_FK from RequisitionList RL inner join
Users u on RL.UserID_FK = u.UserID where RL.statusOfRequest = 'PendingforApproval' and Rl.DeptID_FK = '" + DeptID + "'";
SqlCommand cmd = new SqlCommand(cmdtext, C);
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
RequisitionList St = new RequisitionList();
St.Username = (string)sdr["Username"];
St.RequisitionID = (int)sdr["RequisitionID"];
St.Comments = sdr["Comments"] != DBNull.Value ? (String)sdr["Comments"] : " ";
St.statusOfRequest = (string)sdr["statusOfRequest"];
St.DateofSubmission = (DateTime)sdr["DateofSubmission"];
St.UserID_FK = (int)sdr["UserID_FK"];
St.DeptID_FK = (string)sdr["DeptID_FK"];
Lt_Requisitions.Add(St);
}
return(Lt_Requisitions);
}
}
public void createDisbursementDetail(RequisitionList rl)
{
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
string getItems = @"select Sum(rd.RequisitionQuantity) as amount,rd.ItemID, rl.DeptID_FK, dl.DisbursementID
from RequisitionDetail rd, RequisitionList rl, DisbursementList dl
where rl.DeptID_FK= dl.DepID and rd.RequisitionID= rl.RequisitionID
and dl.DisbursementStatus='pending'" +
"and rl.DeptID_FK='" + rl.DeptID_FK + "' and rl.statusOfRequest='approved'" +
"group by rd.ItemID, rl.DeptID_FK, dl.DisbursementID";
SqlCommand cmd = new SqlCommand(getItems, connection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
RequisitionList ds = new RequisitionList();
ds.amount = (int)reader["amount"];
ds.ItemID = (string)reader["ItemID"];
ds.DeptID_FK = (string)reader["DeptID_FK"];
//ds.RequisitionQuantity = (int)reader["RequisitionQuantity"];
ds.DisbursementID = (int)reader["DisbursementID"];
addIntoDisbursementDetail(ds);
//rlList.Add(ds);
}
}
}
public List <RequisitionList> GetRequisitionList()
{
List <RequisitionList> requisitions = new List <RequisitionList>();
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
string cmdquery = @" select rl.DateofSubmission,rl.RequisitionID,d.Departmentname,rl.DeptID_FK from RequisitionList rl,Department d where rl.DeptID_FK=d.DepartmentID group by d.Departmentname,rl.DeptID_FK,rl.RequisitionID,rl.DateofSubmission";
SqlCommand cmd = new SqlCommand(cmdquery, connection);
//Call Execute reader to get query results
SqlDataReader reader = cmd.ExecuteReader();
//Print out each record
while (reader.Read())
{
RequisitionList s = new RequisitionList();
s.RequisitionID = (int)reader["RequisitionID"];
s.Departmentname = (string)reader["Departmentname"];
s.DeptID_FK = (string)reader["DeptID_FK"];
s.DateofSubmission = (string)reader["DateofSubmission"];
requisitions.Add(s);
}
return(requisitions);
}
}
public List <RequisitionList> getRequisitionList(int UserID)
{
List <RequisitionList> Lt_Requisitions = new List <RequisitionList>();
using (SqlConnection C = new SqlConnection(DataLink.connectionString))
{
C.Open();
string cmdtext = @"SELECT * from RequisitionList WHERE UserID_FK = '" + UserID + "' order by RequisitionID desc";
SqlCommand cmd = new SqlCommand(cmdtext, C);
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
RequisitionList St = new RequisitionList();
St.RequisitionID = (int)sdr[0];
St.statusOfRequest = (string)sdr[1];
St.DateofSubmission = (DateTime)sdr[2];
St.DeptID_FK = (string)sdr[4];
St.UserID_FK = (int)sdr[5];
Lt_Requisitions.Add(St);
}
return(Lt_Requisitions);
}
}
private void LoadRequisitions()
{
String buildingID = rBuildings[cmbBuilding.SelectedIndex].ID.ToString();
String query = "SELECT r.id, r.trnDate, b.Building, r.account, r.processed, r.paid, r.reference, r.contractor, r.payreference, r.amount, r.ledger, b.acc, b.ownbank, b.datapath";
query += " FROM tblRequisition AS r INNER JOIN tblBuildings AS b ON r.building = b.id";
query += " WHERE b.id = " + buildingID + " ORDER BY trnDate";
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
DataSet dsRequisitions = dh.GetData(query, null, out status);
unProcessedRequisitions.Clear();
unPaidRequisitions.Clear();
paidRequisitions.Clear();
if (dsRequisitions != null && dsRequisitions.Tables.Count > 0 && dsRequisitions.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in dsRequisitions.Tables[0].Rows)
{
RequisitionList r = new RequisitionList()
{
ID = Convert.ToString(dr["id"]),
trnDate = Convert.ToDateTime(dr["trnDate"]),
building = Convert.ToString(dr["Building"]),
account = Convert.ToString(dr["account"]),
reference = Convert.ToString(dr["reference"]),
payreference = Convert.ToString(dr["payreference"]),
amount = Convert.ToDouble(dr["amount"]),
ledger = Convert.ToString(dr["ledger"]),
paid = Convert.ToBoolean(dr["paid"]),
};
bool paid = r.paid;
bool matched = false;
bool processed = Convert.ToBoolean(dr["processed"]);
//handled by a batch process now
//String ledger = r.ledger.Split(new String[] { ":" }, StringSplitOptions.None)[0];
//if (r.account.ToUpper() == "TRUST" && !paid)
//{
// matched = GetTransactions(Utilities.GetTrustPath(), r.trnDate, dr["acc"].ToString(), ledger, r.amount);
//}
//else if (!paid)
//{
// matched = GetTransactions(Convert.ToString(dr["datapath"]), r.trnDate, Convert.ToString(dr["ownbank"]), ledger, r.amount * -1);
//}
if (!processed)
{
unProcessedRequisitions.Add(r);
}
else if (!matched && !paid)
{
unPaidRequisitions.Add(r);
}
}
}
}
public void addIntoDisbursementDetail(RequisitionList rl)
{
// System.Guid DisbursementDetailID = System.Guid.NewGuid();
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
string getItems = @"insert into DisbursementDetails(DisbursementID, DepID, ActualQty, ItemID, DeliveredQty) values('" + rl.DisbursementID + "','" + rl.DeptID_FK + "','" + rl.amount + "','" + rl.ItemID + "'," + 0 + ")";
SqlCommand cmd = new SqlCommand(getItems, connection);
cmd.ExecuteNonQuery();
}
}
public void addIntoDisbursementList(RequisitionList rl)
{
string DisbursementStatus = "pending";
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
string getItems = @"insert into DisbursementList( DepID, DisbursementStatus, OTP) values('" + rl.DeptID_FK + "','" + DisbursementStatus + "'," + 0 + ")";
SqlCommand cmd = new SqlCommand(getItems, connection);
cmd.ExecuteNonQuery();
}
}
public static void changeReqListStatusClosed(string rlistNumber)
{
UniversityStoreEntities context = new UniversityStoreEntities();
System.Int32 rln = Convert.ToInt32(rlistNumber);
var query = from x in context.RequisitionLists
where x.RequisitionListNumber == rln
select x;
RequisitionList rl = query.First <RequisitionList>();
rl.Status = STATUS_CLOSED;
context.SaveChanges();
}
public ActionResult ApproveRequisitionDetails(RequisitionList r)
{
Data_RequestDetails ReqDetails = new Data_RequestDetails();
List <WishList> DetailsOfRequest = ReqDetails.RequestDetails(r.RequisitionID);
ViewBag.DetailsOfRequest = DetailsOfRequest;
ViewData["RequestID"] = r.RequisitionID;
ViewData["Dateofsub"] = r.DateofSubmission;
ViewData["status"] = r.statusOfRequest;
return(View());
}
public static RequisitionList addNewReqList(string empNumber)
{
UniversityStoreEntities context = new UniversityStoreEntities();
DateTime d = DateTime.Now;
Employee emp = context.Employees.First <Employee>(x => x.EmployeeNumber == empNumber);
RequisitionList rl = new RequisitionList();
rl.DepartmentCode = emp.DepartmentCode;
rl.EmployeeNumber = emp.EmployeeNumber;
rl.DateCreated = DateTime.Now;
rl.Status = STATUS_PENDING;
context.AddToRequisitionLists(rl);
context.SaveChanges();
return(rl);
}
public ActionResult ViewRequestInfo(RequisitionList r)
{
Data_RequestDetails ReqDetails = new Data_RequestDetails();
List <WishList> DetailsOfRequest = ReqDetails.RequestDetails(r.RequisitionID);
ViewBag.DetailsOfRequest = DetailsOfRequest;
ViewData["RequestID"] = r.RequisitionID;
ViewData["Dateofsub"] = r.DateofSubmission;
ViewData["status"] = r.statusOfRequest;
Users users = Data_Users.GetInfoByUserID((int)Session["UserID"]);
ViewData["Userinfo"] = users;
return(View());
}
public void showAllDeptName()
{
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
string getItems = @"select d.DepartmentID
from Department d";
SqlCommand cmd = new SqlCommand(getItems, connection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
RequisitionList ds = new RequisitionList();
ds.DeptID_FK = (string)reader["DepartmentID"];
createDisbursementDetail(ds);
}
}
}
public ActionResult RejectRequest(int ID, string Comments)
{
Data_CancelRequest DC = new Data_CancelRequest();
if (Comments.Length == 0)
{
DC.RejectRequest(ID);
}
else
{
DC.RejectRequestwithComments(ID, Comments);
}
//return RedirectToAction("ApproveRequisition");
//Email Alert to users for Reject request.
SendEmailNotification sen = new SendEmailNotification();
// Users userInfo = Data_Users.GetInfoByUserID((int)Session["UserID"]);
RequisitionList requestInfo = Data_CancelRequest.RequestInfo(ID);
int empID = (int)requestInfo.UserID_FK;
Users userInfo = Data_Users.GetInfoByUserID(empID);
Department department = Data_Department.GetDepartmentInfoByID(userInfo.DeptID_FK);
String Useremail = userInfo.EmailID;
String EmailSubject = "Request Rejected for RequisitionID#" + ID;
String EmailBody = "<p> Dear " + userInfo.Username + ",</p>";
EmailBody += "<p>Your request " + ID + " has been Rejected by the the your Department Head " + department.DepartmentHead + ". For more details please login into your Account or Please contact your Department Head.";
EmailBody += "<p>Thank you<br/>Logic University Staionery Store</p>";
EmailBody += "<p> Please do not reply to this email it is auto-generated.</p>";
sen.SendEmailHTML(Useremail, EmailSubject, EmailBody);
return(RedirectToAction("ApproveRequisition"));
}
/* public void DeleteRequestDetails(int RequestID)
* {
*
* using (SqlConnection conn = new SqlConnection(DataLink.connectionString))
* {
* conn.Open();
* string cmdtext = @"UPDATE RequisitionList SET statusOfRequest='Cancelled' where RequisitionID = '" + RequestID + "'"; // Delete request details(items ) from Requsistion details tabel
* SqlCommand cmd = new SqlCommand(cmdtext, conn);
* cmd.ExecuteNonQuery();
* }
*
* }*/
public static RequisitionList RequestInfo(int requestID)
{
RequisitionList resquestinfo = new RequisitionList();
using (SqlConnection conn = new SqlConnection(DataLink.connectionString))
{
conn.Open();
string cmdtext = @"select * from RequisitionList where RequisitionID= '" + requestID + "'";
SqlCommand cmd = new SqlCommand(cmdtext, conn);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
//Debug.WriteLine("Hello");
resquestinfo = new RequisitionList();
resquestinfo.UserID_FK = (int)reader["UserID_FK"];
resquestinfo.DeptID_FK = (string)reader["DeptID_FK"];
}
return(resquestinfo);
}
}
public int getRequisitionID()
{
RequisitionList reqInfo = new RequisitionList();
using (SqlConnection conn = new SqlConnection(DataLink.connectionString))
{
conn.Open();
string cmdtext = @"SELECT TOP 1 RequisitionID from RequisitionList WHERE UserID_FK = '" + (int)Session["UserID"]
+ "' AND DateOfSubmission = '" + DateTime.Today.ToString("yyyy-MM-dd") + "' ORDER BY RequisitionID DESC";
SqlCommand cmd = new SqlCommand(cmdtext, conn);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
reqInfo = new RequisitionList();
reqInfo.RequisitionID = (int)reader["RequisitionID"];
}
}
int RequetID = reqInfo.RequisitionID;
return(RequetID);
}
public void createDisbursementList()
{
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
string getItems = @"select d.DepartmentID
from Department d, RequisitionDetail rd, RequisitionList rl, Stationery s
where rd.RequisitionID = rl.RequisitionID and d.DepartmentID = rl.DeptID_FK
and s.ItemID = rd.ItemID and rl.statusOfRequest ='Approved'
group by d.DepartmentID";
SqlCommand cmd = new SqlCommand(getItems, connection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
// System.Guid DisbursementID = System.Guid.NewGuid();
RequisitionList ds = new RequisitionList();
ds.DeptID_FK = (string)reader["DepartmentID"];
addIntoDisbursementList(ds);
}
}
}
