public ApprovedOrdersPage()
{
InitializeComponent();
BindingContext = approvedOrders = new ApprovedOrders();
}
protected String ReturnOrders(string param)
{
String strOutput = "{\"data\":[";
List <ApprovedOrders> ApprovedOrdersList = new List <ApprovedOrders>();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnStringDeltoneCRM"].ConnectionString;
using (SqlCommand cmd = new SqlCommand())
{
if (param == "3")
{
//Modification done here
if (Session["USERPROFILE"].ToString() == "ADMIN")
{
cmd.CommandText = "SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, CT.ContactID, CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime, OD.DueDate, OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID AND OD.CompanyID = CP.CompanyID AND OD.Status='PENDING' ORDER BY OD.OrderID DESC";
}
else if (Session["USERPROFILE"].ToString() == "STANDARD")
{
cmd.CommandText = "SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, CT.ContactID, CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime, OD.DueDate, OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID AND OD.CompanyID = CP.CompanyID AND OD.Status='PENDING' AND CP.OwnershipAdminID = " + Session["LoggedUserID"] + " ORDER BY OD.OrderID DESC";
}
}
else
{
if (param == "1")
{
if (Session["USERPROFILE"].ToString() == "ADMIN")
{
cmd.CommandText = "SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, CT.ContactID, CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime, OD.DueDate, OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID AND OD.CompanyID = CP.CompanyID AND OD.Completed='Y' ORDER BY OD.OrderID DESC";
}
else if (Session["USERPROFILE"].ToString() == "STANDARD")
{
cmd.CommandText = "SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, CT.ContactID, CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime, OD.DueDate, OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID AND OD.CompanyID = CP.CompanyID AND OD.Completed='Y' AND CP.OwnershipAdminID = " + Session["LoggedUserID"] + " ORDER BY OD.OrderID DESC";
}
}
else
{
if (param == "2")
{
if (Session["USERPROFILE"].ToString() == "ADMIN")
{
cmd.CommandText = "SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID, OD.Completed, CT.ContactID, CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime, OD.DueDate, OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID AND OD.CompanyID = CP.CompanyID AND OD.Status='APPROVED' ORDER BY OD.OrderID DESC";
}
else if (Session["USERPROFILE"].ToString() == "STANDARD")
{
cmd.CommandText = "SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, CT.ContactID, CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime, OD.DueDate, OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID AND OD.CompanyID = CP.CompanyID AND OD.Status='APPROVED' AND CP.OwnershipAdminID = " + Session["LoggedUserID"] + " ORDER BY OD.OrderID DESC";
}
}
else
{
if (param == "4")
{
if (Session["USERPROFILE"].ToString() == "ADMIN")
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,
CT.ContactID, CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total,
OD.OrderedDateTime, OD.DueDate ,OD.Completed, OD.SupplierNotes FROM dbo.Orders OD,
dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID AND OD.CompanyID = CP.CompanyID
ORDER BY OD.OrderID DESC";
}
else if (Session["USERPROFILE"].ToString() == "STANDARD")
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID, CT.ContactID, CT.FirstName,
CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime, OD.DueDate ,OD.Completed,
OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND
OD.CompanyID = CT.CompanyID AND OD.CompanyID = CP.CompanyID AND CP.OwnershipAdminID = " + Session["LoggedUserID"] + " ORDER BY OD.OrderID DESC";
}
}
}
}
}
cmd.Connection = conn;
conn.Open();
var status = "";
if (param == "1")
{
status = "COMPLETED";
}
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if (sdr.HasRows)
{
while (sdr.Read())
{
//EditOrder(OrderID, CompanyID, ContactID)
// String InvoiceNum = String.Empty;
// //Modified Here
// InvoiceNum = sdr["XeroInvoiceNumber"].ToString();
// String strEditOrder = "<img src='../Images/Edit.png' onclick='openWin(" + sdr["OrderID"].ToString() + "," + sdr["ContactID"] + "," + sdr["CompanyID"] + ");'>";
// String Total = sdr["Total"].ToString();
// Decimal ConvertedTotal = Math.Round(Convert.ToDecimal(Total), 2);
//// String OrderDate = sdr["OrderedDateTime"].ToString();
// // String DueDate = sdr["DueDate"].ToString();
// // String CreatedBy = sdr["CreatedBy"].ToString();
// String Link = "<a class='details' href='#'>DETAILS</a>";
// String finalSupNotes = String.Empty;
// status = (sdr["Completed"] != DBNull.Value) ? status = "COMPLETED" : sdr["Status"].ToString();
// if (sdr["SupplierNotes"].ToString() != "")
// {
// finalSupNotes = sdr["SupplierNotes"].ToString().Substring(0, 3);
// }
// else
// {
// finalSupNotes = "";
// }
// strOutput = strOutput + "[\"" + Link + "\"," + "\"" + sdr["OrderID"] + "\","
// + "\"" + sdr["CompanyName"] + "\"," + "\"" + sdr["FirstName"] + " "
// + sdr["LastName"] + "\"," + "\"" + sdr["XeroInvoiceNumber"].ToString() + "\"," + "\"" + "$" + ConvertedTotal
// + "\"," + "\"" + sdr["OrderedDateTime"].ToString() + "\"," + "\"" + sdr["DueDate"].ToString() + "\"," + "\"" + sdr["CreatedBy"].ToString()
// + "\"," + "\"" + status + "\"," + "\"" + finalSupNotes + "\"," + "\"" + strEditOrder + "\"],";
String finalSuppNotes = String.Empty;
ApprovedOrders item = new ApprovedOrders();
item.OrderNo = sdr["OrderID"].ToString();
item.CompanyName = sdr["CompanyName"].ToString();
item.ContactPerson = sdr["FirstName"].ToString();
item.InvNumber = sdr["XeroInvoiceNumber"].ToString();
item.OrderTotal = float.Parse(sdr["Total"].ToString());
item.OrderedDate = sdr["OrderedDateTime"].ToString();
item.DueDate = sdr["DueDate"].ToString();
item.CreatedBy = sdr["CreatedBy"].ToString();
item.Status = sdr["Status"].ToString();
if (sdr["SupplierNotes"].ToString() != "")
{
finalSuppNotes = sdr["SupplierNotes"].ToString().Substring(0, 3);
}
else
{
finalSuppNotes = "";
}
item.SupplierNotes = finalSuppNotes;
item.ViewEdit = "<img src='../Images/Edit.png' onclick='openWin(" + sdr["OrderID"].ToString() + "," + sdr["ContactID"] + "," + sdr["CompanyID"] + ");'>";
ApprovedOrdersList.Add(item);
}
//int Length = strOutput.Length;
//strOutput = strOutput.Substring(0, (Length - 1));
}
}
// strOutput = strOutput + "]}";
conn.Close();
}
}
var result = new
{
iTotalRecords = ApprovedOrdersList.Count(),
iTotalDisplayRecords = 25,
aaData = ApprovedOrdersList
};
JavaScriptSerializer js = new JavaScriptSerializer();
js.MaxJsonLength = 5000000;
return(js.Serialize(result));
}
public void ProcessRequest(HttpContext context)
{
int displayLength = int.Parse(context.Request["iDisplayLength"]);
int displayStart = int.Parse(context.Request["iDisplayStart"]);
int sortCol = int.Parse(context.Request["iSortCol_0"]);
string sortDir = context.Request["sSortDir_0"];
string search = context.Request["sSearch"];
String querylevel = String.Empty;
querylevel = context.Session["USERPROFILE"].ToString();
String loggeduser = String.Empty;
loggeduser = context.Session["LoggedUserID"].ToString();
String cs = ConfigurationManager.ConnectionStrings["ConnStringDeltoneCRM"].ConnectionString;
List <ApprovedOrders> ApprovedOrdersList = new List <ApprovedOrders>();
int filteredCount = 0;
using (SqlConnection conn = new SqlConnection(cs))
{
SqlCommand cmd;
if (querylevel == "ADMIN")
{
cmd = new SqlCommand("getAllApprovedOrders", conn);
}
else
{
cmd = new SqlCommand("getAllApprovedOrdersStandardUsers", conn);
}
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paramDisplayLength = new SqlParameter()
{
ParameterName = "@DisplayLength",
Value = displayLength
};
cmd.Parameters.Add(paramDisplayLength);
SqlParameter paramDisplayStart = new SqlParameter()
{
ParameterName = "@DisplayStart",
Value = displayStart
};
cmd.Parameters.Add(paramDisplayStart);
SqlParameter paramSortCol = new SqlParameter()
{
ParameterName = "@SortCol",
Value = sortCol
};
cmd.Parameters.Add(paramSortCol);
SqlParameter paramSortDir = new SqlParameter()
{
ParameterName = "@SortDir",
Value = sortDir
};
cmd.Parameters.Add(paramSortDir);
SqlParameter paramSearchString = new SqlParameter()
{
ParameterName = "@Search",
Value = search
};
cmd.Parameters.Add(paramSearchString);
SqlParameter paramLoggedUser = new SqlParameter()
{
ParameterName = "@LoggedUser",
Value = loggeduser
};
cmd.Parameters.Add(paramLoggedUser);
conn.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
String finalSuppNotes = String.Empty;
ApprovedOrders item = new ApprovedOrders();
item.OrderNo = sdr["OrderID"].ToString();
filteredCount = Convert.ToInt32(sdr["TotalCount"]);
item.CompanyName = sdr["CompanyName"].ToString();
item.ContactPerson = sdr["FullName"].ToString();
var contactNameOrder = GetContactOrderName(item.OrderNo);
if (!string.IsNullOrEmpty(contactNameOrder))
{
item.ContactPerson = contactNameOrder;
}
item.InvNumber = sdr["XeroInvoiceNumber"].ToString();
item.OrderTotal = float.Parse(sdr["Total"].ToString());
item.CreatedDate = Convert.ToDateTime(sdr["CreatedDate"].ToString()).ToShortDateString();
var orderedDate = Convert.ToDateTime(sdr["CreatedDate"].ToString());
item.OrderedDate = Convert.ToDateTime(sdr["OrderedDate"].ToString()).ToShortDateString();
item.DueDate = Convert.ToDateTime(sdr["DDate"].ToString()).ToShortDateString();
item.CanFlag = "0";
var canCAllFlag = CheckInvoiceDate(item.OrderNo, orderedDate, context);
if (canCAllFlag)
{
item.CanFlag = "1";
}
item.CreatedBy = sdr["OrderedBy"].ToString();
item.Status = sdr["Status"].ToString();
if (sdr["SupplierNotes"].ToString() != "")
{
if (sdr["SupplierNotes"].ToString().Length > 3)
{
finalSuppNotes = sdr["SupplierNotes"].ToString().Substring(0, 3);
}
else
{
finalSuppNotes = sdr["SupplierNotes"].ToString();
}
}
else
{
finalSuppNotes = "";
}
item.SupplierNotes = finalSuppNotes;
item.ViewEdit = "<img src='../Images/Edit.png' onclick='openWin(" + sdr["OrderID"].ToString() + "," + sdr["ContactID"] + "," + sdr["CompanyID"] + ");'>";
ApprovedOrdersList.Add(item);
}
conn.Close();
}
var result = new
{
iTotalRecords = getItemsTotalCount(querylevel, loggeduser),
iTotalDisplayRecords = filteredCount,
aaData = ApprovedOrdersList
};
JavaScriptSerializer js = new JavaScriptSerializer();
context.Response.Write(js.Serialize(result));
}
protected String ReturnOrders(string param, HttpContext context)
{
List <ApprovedOrders> ApprovedOrdersList = new List <ApprovedOrders>();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnStringDeltoneCRM"].ConnectionString;
using (SqlCommand cmd = new SqlCommand())
{
if (param == "5")
{
//Modification done here
if (context.Session["USERPROFILE"].ToString() == "ADMIN")
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, CT.ContactID, OrderContactName,
CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime,OD.CreatedDateTime, OD.DueDate, OD.SupplierNotes
FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID
AND OD.CompanyID = CP.CompanyID AND OD.Status='BO' ORDER BY OD.OrderID DESC";
}
else
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, CT.ContactID, OrderContactName,
CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime,OD.CreatedDateTime, OD.DueDate, OD.SupplierNotes
FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID
AND OD.CompanyID = CP.CompanyID AND OD.Status='BO' AND CP.OwnershipAdminID = " + context.Session["LoggedUserID"] + " ORDER BY OD.OrderID DESC";
}
}
else
if (param == "6")
{
//Modification done here
if (context.Session["USERPROFILE"].ToString() == "ADMIN")
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, CT.ContactID, OrderContactName,
CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime,OD.CreatedDateTime, OD.DueDate, OD.SupplierNotes
FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID
AND OD.CompanyID = CP.CompanyID AND OD.Status='EOM' ORDER BY OD.OrderID DESC";
}
else
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, OrderContactName,
CT.ContactID, CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime,OD.CreatedDateTime, OD.DueDate,
OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID
AND OD.CompanyID = CP.CompanyID AND OD.Status='EOM' AND CP.OwnershipAdminID = " + context.Session["LoggedUserID"] + " ORDER BY OD.OrderID DESC";
}
}
else
if (param == "7")
{
//Modification done here
if (context.Session["USERPROFILE"].ToString() == "ADMIN")
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, CT.ContactID, OrderContactName,
CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime,OD.CreatedDateTime, OD.DueDate,
OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID
AND OD.CompanyID = CP.CompanyID AND ( OD.Status='INHOUSE' OR OD.Status='TW-INHOUSE') ORDER BY OD.OrderID DESC";
}
else
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, CT.ContactID, OrderContactName,
CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime,OD.CreatedDateTime, OD.DueDate,
OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID
AND OD.CompanyID = CP.CompanyID AND ( OD.Status='INHOUSE' OR OD.Status='TW-INHOUSE') AND CP.OwnershipAdminID = " + context.Session["LoggedUserID"] + " ORDER BY OD.OrderID DESC";
}
}
else
if (param == "8")
{
//Modification done here
if (context.Session["USERPROFILE"].ToString() == "ADMIN")
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, CT.ContactID, OrderContactName,
CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime,OD.CreatedDateTime, OD.DueDate,
OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID
AND OD.CompanyID = CP.CompanyID AND OD.Status='TW-INHOUSE' ORDER BY OD.OrderID DESC";
}
else
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, CT.ContactID, OrderContactName,
CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime,OD.CreatedDateTime, OD.DueDate,
OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID
AND OD.CompanyID = CP.CompanyID AND OD.Status='TW-INHOUSE' AND CP.OwnershipAdminID = " + context.Session["LoggedUserID"] + " ORDER BY OD.OrderID DESC";
}
}
else
if (param == "3")
{
//Modification done here
if (context.Session["USERPROFILE"].ToString() == "ADMIN")
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, OrderContactName,
CT.ContactID, CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime,OD.CreatedDateTime, OD.DueDate,
OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID
AND OD.CompanyID = CP.CompanyID AND OD.Status='PENDING' ORDER BY OD.OrderID DESC";
}
else
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, CT.ContactID, OrderContactName,
CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime,OD.CreatedDateTime, OD.DueDate, OD.SupplierNotes
FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID
AND OD.CompanyID = CP.CompanyID AND OD.Status='PENDING' AND CP.OwnershipAdminID = " + context.Session["LoggedUserID"] + " ORDER BY OD.OrderID DESC";
}
}
else
{
if (param == "1")
{
if (context.Session["USERPROFILE"].ToString() == "ADMIN")
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, OrderContactName,
CT.ContactID, CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime,OD.CreatedDateTime, OD.DueDate,
OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID
AND OD.CompanyID = CP.CompanyID AND OD.Status='COMPLETED' ORDER BY OD.OrderID DESC";
}
else
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, OrderContactName,
CT.ContactID, CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime, OD.CreatedDateTime, OD.DueDate,
OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID
AND OD.CompanyID = CT.CompanyID AND OD.CompanyID = CP.CompanyID
AND OD.Status='COMPLETED' AND CP.OwnershipAdminID = " + context.Session["LoggedUserID"] + " ORDER BY OD.OrderID DESC";
}
}
else
{
if (param == "2")
{
if (context.Session["USERPROFILE"].ToString() == "ADMIN")
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID, OD.Completed,OrderContactName,
CT.ContactID, CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime,OD.CreatedDateTime, OD.DueDate,
OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID
AND OD.CompanyID = CP.CompanyID AND OD.Status='APPROVED' ORDER BY OD.OrderID DESC";
}
else if (context.Session["USERPROFILE"].ToString() == "STANDARD")
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID,OD.Completed, OrderContactName,
CT.ContactID, CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime, OD.CreatedDateTime,OD.DueDate,
OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID
AND OD.CompanyID = CP.CompanyID AND OD.Status='APPROVED' AND CP.OwnershipAdminID = " + context.Session["LoggedUserID"] + " ORDER BY OD.OrderID DESC";
}
}
else
{
if (param == "4")
{
if (context.Session["USERPROFILE"].ToString() == "ADMIN")
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID, OrderContactName,
CT.ContactID, CT.FirstName, CT.LastName, OD.CreatedBy,OD.Status, OD.Total,
OD.OrderedDateTime, OD.CreatedDateTime,OD.DueDate ,OD.Completed, OD.SupplierNotes FROM dbo.Orders OD,
dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND OD.CompanyID = CT.CompanyID AND OD.CompanyID = CP.CompanyID
ORDER BY OD.OrderID DESC";
}
else
{
cmd.CommandText = @"SELECT OD.OrderID,OD.XeroInvoiceNumber, CP.CompanyName, CP.CompanyID, CT.ContactID, CT.FirstName,OrderContactName,
CT.LastName, OD.CreatedBy,OD.Status, OD.Total, OD.OrderedDateTime,OD.CreatedDateTime, OD.DueDate ,OD.Completed,
OD.SupplierNotes FROM dbo.Orders OD, dbo.Contacts CT, dbo.Companies CP WHERE OD.ContactID = CT.ContactID AND
OD.CompanyID = CT.CompanyID AND OD.CompanyID = CP.CompanyID AND CP.OwnershipAdminID = " + context.Session["LoggedUserID"] + " ORDER BY OD.OrderID DESC";
}
}
}
}
}
cmd.Connection = conn;
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if (sdr.HasRows)
{
while (sdr.Read())
{
String finalSuppNotes = String.Empty;
ApprovedOrders item = new ApprovedOrders();
item.OrderNo = sdr["OrderID"].ToString();
item.CompanyName = sdr["CompanyName"].ToString();
item.ContactPerson = sdr["FirstName"].ToString();
if (sdr["OrderContactName"] != DBNull.Value)
{
item.ContactPerson = sdr["OrderContactName"].ToString();
}
item.InvNumber = sdr["XeroInvoiceNumber"].ToString();
item.OrderTotal = float.Parse(sdr["Total"].ToString());
item.CreatedDate = Convert.ToDateTime(sdr["CreatedDateTime"].ToString()).ToShortDateString();
item.OrderedDate = Convert.ToDateTime(sdr["OrderedDateTime"].ToString()).ToShortDateString();
item.DueDate = Convert.ToDateTime(sdr["DueDate"].ToString()).ToShortDateString();
item.CreatedBy = sdr["CreatedBy"].ToString();
item.Status = sdr["Status"].ToString();
if (sdr["SupplierNotes"].ToString() != "")
{
if (sdr["SupplierNotes"].ToString().Length > 3)
{
finalSuppNotes = sdr["SupplierNotes"].ToString().Substring(0, 3);
}
else
{
finalSuppNotes = sdr["SupplierNotes"].ToString();
}
}
else
{
finalSuppNotes = "";
}
item.SupplierNotes = finalSuppNotes;
item.ViewEdit = "<img src='../Images/Edit.png' onclick='openWin(" + sdr["OrderID"].ToString() + "," + sdr["ContactID"] + "," + sdr["CompanyID"] + ");'>";
ApprovedOrdersList.Add(item);
}
}
}
// strOutput = strOutput + "]}";
conn.Close();
}
}
var result = new
{
iTotalRecords = ApprovedOrdersList.Count(),
iTotalDisplayRecords = 25,
aaData = ApprovedOrdersList
};
JavaScriptSerializer js = new JavaScriptSerializer();
js.MaxJsonLength = 50000000;
return(js.Serialize(result));
}
