public Boolean ApproveProjectHeader(projectheader prevpheader)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update ProjectHeader set DocumentStatus=99" +
", Status=1" +
", ApproveUser='******'" +
", TrackingNo=" + prevpheader.TrackingNo +
", TrackingDate=convert(date, getdate())" +
" where DocumentID='" + prevpheader.DocumentID +
"' and TemporaryNo=" + prevpheader.TemporaryNo +
" and TemporaryDate='" + prevpheader.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("approve", "ProjectHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean reverseProjectHeader(projectheader prevpheader)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update ProjectHeader set DocumentStatus=" + prevpheader.DocumentStatus +
", forwardUser='******'" +
", ForwarderList='" + prevpheader.ForwarderList + "'" +
" where DocumentID='" + prevpheader.DocumentID + "'" +
" and TemporaryNo=" + prevpheader.TemporaryNo +
" and TemporaryDate='" + prevpheader.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "ProjectHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean updateProjectHeader(projectheader pheader, projectheader prevpheader)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update ProjectHeader set CustomerID='" + pheader.CustomerID + "'" +
", Status=" + pheader.Status +
", ProjectID ='" + pheader.ProjectID +
"', OfficeID ='" + pheader.OfficeID +
"', ProjectManager='" + pheader.ProjectManager +
"', StartDate='" + pheader.StartDate.ToString("yyyy-MM-dd") +
"', TargetDate='" + pheader.TargetDate.ToString("yyyy-MM-dd") +
"', ShortDescription='" + pheader.ShortDescription +
"', ForwarderList='" + pheader.ForwarderList +
"' where DocumentID='" + prevpheader.DocumentID +
"' and TemporaryNo=" + prevpheader.TemporaryNo +
" and TemporaryDate='" + prevpheader.TemporaryDate.ToString("yyyy-MM-dd") + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "ProjectHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean validateProjectHeader(projectheader pheader)
{
Boolean status = true;
try
{
if (pheader.DocumentID.Trim().Length == 0 || pheader.DocumentID == null)
{
return(false);
}
if (pheader.ProjectID.Trim().Length == 0 || pheader.ProjectID == null)
{
return(false);
}
if (pheader.OfficeID.Trim().Length == 0 || pheader.OfficeID == null)
{
return(false);
}
//if (pheader.Status <= 0)
//{
// return false;
//}
if (pheader.ProjectManager.Trim().Length == 0 || pheader.ProjectManager == null)
{
return(false);
}
if (pheader.ShortDescription.Trim().Length == 0 || pheader.ShortDescription == null)
{
return(false);
}
if (pheader.CustomerID.Trim().Length == 0 || pheader.CustomerID == null)
{
return(false);
}
if (pheader.StartDate == null)
{
return(false);
}
if (pheader.TargetDate < DateTime.Now)
{
return(false);
}
if (pheader.TargetDate < DateTime.Now || pheader.TargetDate < pheader.StartDate || pheader.TargetDate == null)
{
return(false);
}
}
catch (Exception)
{
MessageBox.Show("Failed to insert Project Header.");
}
return(status);
}
public Boolean insertProjectHeader(projectheader pheader)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "insert into ProjectHeader " +
" (DocumentID,TemporaryNo,TemporaryDate,TrackingNo,TrackingDate," +
"ProjectID,OfficeID,ProjectManager,ShortDescription,CustomerID,StartDate,TargetDate,DocumentStatus," +
"Status,CreateUser,CreateTime,ForwarderList)" +
"values (" +
"'" + pheader.DocumentID + "'," +
pheader.TemporaryNo + "," +
"'" + pheader.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
pheader.TrackingNo + "," +
"'" + pheader.TrackingDate.ToString("yyyy-MM-dd") + "'," +
"'" + pheader.ProjectID + "'," +
"'" + pheader.OfficeID + "'," +
"'" + pheader.ProjectManager + "'," +
"'" + pheader.ShortDescription + "'," +
"'" + pheader.CustomerID + "'," +
"'" + pheader.StartDate.ToString("yyyy-MM-dd") + "'," +
"'" + pheader.TargetDate.ToString("yyyy-MM-dd") + "'," +
pheader.DocumentStatus + "," +
pheader.Status + "," +
"'" + Login.userLoggedIn + "'," +
"GETDATE()" +
",'" + pheader.ForwarderList + "')";
//"'" + pheader.ForwardUser + "'," +
//"'" + pheader.ApproveUser + "'," +
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "ProjectHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public List <projectheader> getFilteredProject()
{
projectheader pheader;
List <projectheader> pheaderlist = new List <projectheader>();
try
{
string query = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," +
" TrackingNo,TrackingDate,ProjectID,ProjectManager,ProjectManagerName,ShortDescription,CustomerID,CustomerName,StartDate," +
" TargetDate,Status,DocumentStatus,CreateTime, " +
"CreateUser,ForwardUser,ApproveUser,CreatorName,ForwarderName,ApproverName,ForwarderList,OfficeID,OfficeName,ProjectStatus" +
" from ViewProjectHeader" +
" where DocumentStatus = 99 and Status = 1 order by ProjectID";
SqlConnection conn = new SqlConnection(Login.connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
pheader = new projectheader();
pheader.rowID = reader.GetInt32(0);
pheader.DocumentID = reader.GetString(1);
pheader.DocumentName = reader.GetString(2);
pheader.TemporaryNo = reader.GetInt32(3);
pheader.TemporaryDate = reader.GetDateTime(4);
pheader.TrackingNo = reader.GetInt32(5);
pheader.TrackingDate = reader.GetDateTime(6);
pheader.ProjectID = reader.IsDBNull(7) ? "" : reader.GetString(7);
pheader.ProjectManager = reader.IsDBNull(8) ? "" : reader.GetString(8);
pheader.ProjectManagerName = reader.IsDBNull(9) ? "" : reader.GetString(9);
pheader.ShortDescription = reader.IsDBNull(10) ? "" : reader.GetString(10);
pheader.CustomerID = reader.IsDBNull(11) ? "" : reader.GetString(11);
pheader.CustomerName = reader.IsDBNull(12) ? "" : reader.GetString(12);
pheader.StartDate = reader.GetDateTime(13);
pheader.TargetDate = reader.GetDateTime(14);
pheader.Status = reader.GetInt32(15);
pheader.DocumentStatus = reader.GetInt32(16);
pheader.CreateTime = reader.GetDateTime(17);
pheader.CreateUser = reader.GetString(18);
pheader.ForwardUser = reader.GetString(19);
pheader.ApproveUser = reader.GetString(20);
pheader.CreatorName = reader.GetString(21);
pheader.ForwarderName = reader.GetString(22);
pheader.ApproverName = reader.GetString(23);
if (!reader.IsDBNull(24))
{
pheader.ForwarderList = reader.GetString(24);
}
else
{
pheader.ForwarderList = "";
}
pheader.OfficeID = reader.IsDBNull(25) ? "" : reader.GetString(25);
pheader.OfficeName = reader.IsDBNull(26) ? "" : reader.GetString(26);
pheader.ProjectStatus = reader.GetInt32(27);
pheaderlist.Add(pheader);
}
conn.Close();
}
catch (Exception)
{
MessageBox.Show("Error querying Project Data");
}
return(pheaderlist);
}
public List <projectheader> getFilteredProjectHeader(string userList, int opt)
{
projectheader pheader;
List <projectheader> pheaderlist = new List <projectheader>();
try
{
string query1 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," +
" TrackingNo,TrackingDate,ProjectID,ProjectManager,ProjectManagerName,ShortDescription,CustomerID,CustomerName,StartDate," +
" TargetDate,Status,DocumentStatus,CreateTime, " +
"CreateUser,ForwardUser,ApproveUser,CreatorName,ForwarderName,ApproverName , ForwarderList,OfficeID,OfficeName" +
" from ViewProjectHeader" +
" where ((forwarduser='******' and DocumentStatus between 2 and 98) " +
" or (createuser='******' and DocumentStatus=1))" +
" order by TemporaryNo";
string query2 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," +
" TrackingNo,TrackingDate,ProjectID,ProjectManager,ProjectManagerName,ShortDescription,CustomerID,CustomerName,StartDate," +
" TargetDate,Status,DocumentStatus,CreateTime, " +
"CreateUser,ForwardUser,ApproveUser,CreatorName,ForwarderName,ApproverName,ForwarderList,OfficeID,OfficeName" +
" from ViewProjectHeader" +
" where ((createuser='******' and DocumentStatus between 2 and 98 ) " +
" or (ForwarderList like '%" + userList + "%' and DocumentStatus between 2 and 98 and ForwardUser <> '" + Login.userLoggedIn + "'))" +
" order by TemporaryNo";
string query3 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," +
" TrackingNo,TrackingDate,ProjectID,ProjectManager,ProjectManagerName,ShortDescription,CustomerID,CustomerName,StartDate," +
" TargetDate,Status,DocumentStatus,CreateTime, " +
"CreateUser,ForwardUser,ApproveUser,CreatorName,ForwarderName,ApproverName,ForwarderList,OfficeID,OfficeName" +
" from ViewProjectHeader" +
" where ((createuser='******'" +
" or ForwarderList like '%" + userList + "%'" +
" or approveUser='******')" +
" and DocumentStatus = 99) order by TemporaryNo";
string query6 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," +
" TrackingNo,TrackingDate,ProjectID,ProjectManager,ProjectManagerName,ShortDescription,CustomerID,CustomerName,StartDate," +
" TargetDate,Status,DocumentStatus,CreateTime, " +
"CreateUser,ForwardUser,ApproveUser,CreatorName,ForwarderName,ApproverName,ForwarderList,OfficeID,OfficeName" +
" from ViewProjectHeader" +
" where DocumentStatus = 99 and Status = 1 order by ProjectID";
SqlConnection conn = new SqlConnection(Login.connString);
string query = "";
switch (opt)
{
case 1:
query = query1;
break;
case 2:
query = query2;
break;
case 3:
query = query3;
break;
case 6:
query = query6;
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
pheader = new projectheader();
pheader.rowID = reader.GetInt32(0);
pheader.DocumentID = reader.GetString(1);
pheader.DocumentName = reader.GetString(2);
pheader.TemporaryNo = reader.GetInt32(3);
pheader.TemporaryDate = reader.GetDateTime(4);
pheader.TrackingNo = reader.GetInt32(5);
pheader.TrackingDate = reader.GetDateTime(6);
pheader.ProjectID = reader.IsDBNull(7) ? "" : reader.GetString(7);
pheader.ProjectManager = reader.IsDBNull(8) ? "" : reader.GetString(8);
pheader.ProjectManagerName = reader.IsDBNull(9) ? "" : reader.GetString(9);
pheader.ShortDescription = reader.IsDBNull(10) ? "" : reader.GetString(10);
pheader.CustomerID = reader.IsDBNull(11) ? "" : reader.GetString(11);
pheader.CustomerName = reader.IsDBNull(12) ? "" : reader.GetString(12);
pheader.StartDate = reader.GetDateTime(13);
pheader.TargetDate = reader.GetDateTime(14);
pheader.Status = reader.GetInt32(15);
pheader.DocumentStatus = reader.GetInt32(16);
pheader.CreateTime = reader.GetDateTime(17);
pheader.CreateUser = reader.GetString(18);
pheader.ForwardUser = reader.GetString(19);
pheader.ApproveUser = reader.GetString(20);
pheader.CreatorName = reader.GetString(21);
pheader.ForwarderName = reader.GetString(22);
pheader.ApproverName = reader.GetString(23);
if (!reader.IsDBNull(24))
{
pheader.ForwarderList = reader.GetString(24);
}
else
{
pheader.ForwarderList = "";
}
pheader.OfficeID = reader.IsDBNull(25) ? "" : reader.GetString(25);
pheader.OfficeName = reader.IsDBNull(26) ? "" : reader.GetString(26);
pheaderlist.Add(pheader);
}
conn.Close();
}
catch (Exception)
{
MessageBox.Show("Error querying Project Header Data");
}
return(pheaderlist);
}