public static List <ProjectStatus> List()
{
List <ProjectStatus> ProjectStatusList = new List <ProjectStatus>();
SqlConnection connection = PMMSData.GetConnection();
String selectProcedure = "[Project_ProjectStatusSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
ProjectStatus ProjectStatus = new ProjectStatus();
while (reader.Read())
{
ProjectStatus = new ProjectStatus();
ProjectStatus.ProjectStatusID = System.Convert.ToInt32(reader["ProjectStatusID"]);
ProjectStatus.ProjectStatusName = Convert.ToString(reader["ProjectStatusName"]);
ProjectStatusList.Add(ProjectStatus);
}
reader.Close();
}
catch (SqlException)
{
return(ProjectStatusList);
}
finally
{
connection.Close();
}
return(ProjectStatusList);
}
public static List <AdvancePayment> List()
{
List <AdvancePayment> AdvancePaymentList = new List <AdvancePayment>();
SqlConnection connection = PMMSData.GetConnection();
String selectProcedure = "[InvoiceAdvance_AdvancePaymentSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
AdvancePayment AdvancePayment = new AdvancePayment();
while (reader.Read())
{
AdvancePayment = new AdvancePayment();
AdvancePayment.AdvancePaymentID = System.Convert.ToInt32(reader["AdvancePaymentID"]);
AdvancePaymentList.Add(AdvancePayment);
}
reader.Close();
}
catch (SqlException)
{
return(AdvancePaymentList);
}
finally
{
connection.Close();
}
return(AdvancePaymentList);
}
public static List <Company> List()
{
List <Company> CompanyList = new List <Company>();
SqlConnection connection = PMMSData.GetConnection();
String selectProcedure = "[Project_CompanySelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
Company Company = new Company();
while (reader.Read())
{
Company = new Company();
Company.CompanyID = System.Convert.ToInt32(reader["CompanyID"]);
Company.CompanyName = Convert.ToString(reader["CompanyName"]);
CompanyList.Add(Company);
}
reader.Close();
}
catch (SqlException)
{
return(CompanyList);
}
finally
{
connection.Close();
}
return(CompanyList);
}
public static DataTable SelectAll()
{
SqlConnection connection = PMMSData.GetConnection();
string selectProcedure = "[InvoiceItem_InvoiceSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
selectCommand.CommandType = CommandType.StoredProcedure;
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
}
reader.Close();
}
catch (SqlException)
{
return(dt);
}
finally
{
connection.Close();
}
return(dt);
}
public static ProjectStatus Select_Record(ProjectStatus ProjectStatusPara)
{
ProjectStatus ProjectStatus = new ProjectStatus();
SqlConnection connection = PMMSData.GetConnection();
string selectProcedure = "[ProjectStatusSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@ProjectStatusID", ProjectStatusPara.ProjectStatusID);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
ProjectStatus.ProjectStatusID = System.Convert.ToInt32(reader["ProjectStatusID"]);
ProjectStatus.ProjectStatusName = System.Convert.ToString(reader["ProjectStatusName"]);
}
else
{
ProjectStatus = null;
}
reader.Close();
}
catch (SqlException)
{
return(ProjectStatus);
}
finally
{
connection.Close();
}
return(ProjectStatus);
}
public static List <Client> List()
{
List <Client> ClientList = new List <Client>();
SqlConnection connection = PMMSData.GetConnection();
String selectProcedure = "[Invoice_ClientSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
Client Client = new Client();
while (reader.Read())
{
Client = new Client();
Client.ClientID = System.Convert.ToInt32(reader["ClientID"]);
Client.ClientName = Convert.ToString(reader["ClientName"]);
ClientList.Add(Client);
}
reader.Close();
}
catch (SqlException)
{
return(ClientList);
}
finally
{
connection.Close();
}
return(ClientList);
}
public static UserType Select_Record(UserType UserTypePara)
{
UserType UserType = new UserType();
SqlConnection connection = PMMSData.GetConnection();
string selectProcedure = "[UserTypeSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@UserTypeID", UserTypePara.UserTypeID);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
UserType.UserTypeID = System.Convert.ToInt32(reader["UserTypeID"]);
UserType.UserTypeName = System.Convert.ToString(reader["UserTypeName"]);
}
else
{
UserType = null;
}
reader.Close();
}
catch (SqlException)
{
return(UserType);
}
finally
{
connection.Close();
}
return(UserType);
}
public static List <Invoice> List()
{
List <Invoice> InvoiceList = new List <Invoice>();
SqlConnection connection = PMMSData.GetConnection();
String selectProcedure = "[InvoiceItem_InvoiceSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
Invoice Invoice = new Invoice();
while (reader.Read())
{
Invoice = new Invoice();
Invoice.InvoiceID = System.Convert.ToInt32(reader["InvoiceID"]);
InvoiceList.Add(Invoice);
}
reader.Close();
}
catch (SqlException)
{
return(InvoiceList);
}
finally
{
connection.Close();
}
return(InvoiceList);
}
public static List <Employee> List()
{
List <Employee> EmployeeList = new List <Employee>();
SqlConnection connection = PMMSData.GetConnection();
String selectProcedure = "[Timesheet_EmployeeSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
Employee Employee = new Employee();
while (reader.Read())
{
Employee = new Employee();
Employee.EmployeeID = System.Convert.ToInt32(reader["EmployeeID"]);
Employee.FirstName = Convert.ToString(reader["FirstName"]);
EmployeeList.Add(Employee);
}
reader.Close();
}
catch (SqlException)
{
return(EmployeeList);
}
finally
{
connection.Close();
}
return(EmployeeList);
}
public static List <Task> List()
{
List <Task> TaskList = new List <Task>();
SqlConnection connection = PMMSData.GetConnection();
String selectProcedure = "[TaskAssignment_TaskSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
Task Task = new Task();
while (reader.Read())
{
Task = new Task();
Task.TaskID = System.Convert.ToInt32(reader["TaskID"]);
Task.TaskName = Convert.ToString(reader["TaskName"]);
TaskList.Add(Task);
}
reader.Close();
}
catch (SqlException)
{
return(TaskList);
}
finally
{
connection.Close();
}
return(TaskList);
}
public static List <UserType> List()
{
List <UserType> UserTypeList = new List <UserType>();
SqlConnection connection = PMMSData.GetConnection();
String selectProcedure = "[UserTypePermission_UserTypeSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
UserType UserType = new UserType();
while (reader.Read())
{
UserType = new UserType();
UserType.UserTypeID = System.Convert.ToInt32(reader["UserTypeID"]);
UserType.UserTypeName = Convert.ToString(reader["UserTypeName"]);
UserTypeList.Add(UserType);
}
reader.Close();
}
catch (SqlException)
{
return(UserTypeList);
}
finally
{
connection.Close();
}
return(UserTypeList);
}
public static bool Add(InvoiceAdvance InvoiceAdvance)
{
SqlConnection connection = PMMSData.GetConnection();
string insertProcedure = "[InvoiceAdvanceInsert]";
SqlCommand insertCommand = new SqlCommand(insertProcedure, connection);
insertCommand.CommandType = CommandType.StoredProcedure;
insertCommand.Parameters.AddWithValue("@InvoiceID", InvoiceAdvance.InvoiceID);
insertCommand.Parameters.AddWithValue("@AdvancePaymentID", InvoiceAdvance.AdvancePaymentID);
insertCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
insertCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
int count = System.Convert.ToInt32(insertCommand.Parameters["@ReturnValue"].Value);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static bool Delete(ProjectStatus ProjectStatus)
{
SqlConnection connection = PMMSData.GetConnection();
string deleteProcedure = "[ProjectStatusDelete]";
SqlCommand deleteCommand = new SqlCommand(deleteProcedure, connection);
deleteCommand.CommandType = CommandType.StoredProcedure;
deleteCommand.Parameters.AddWithValue("@OldProjectStatusID", ProjectStatus.ProjectStatusID);
deleteCommand.Parameters.AddWithValue("@OldProjectStatusName", ProjectStatus.ProjectStatusName);
deleteCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
deleteCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
try
{
connection.Open();
deleteCommand.ExecuteNonQuery();
int count = System.Convert.ToInt32(deleteCommand.Parameters["@ReturnValue"].Value);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static List <Permission> List()
{
List <Permission> PermissionList = new List <Permission>();
SqlConnection connection = PMMSData.GetConnection();
String selectProcedure = "[UserTypePermission_PermissionSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
Permission Permission = new Permission();
while (reader.Read())
{
Permission = new Permission();
Permission.PermissionID = System.Convert.ToInt32(reader["PermissionID"]);
Permission.PermissionName = Convert.ToString(reader["PermissionName"]);
PermissionList.Add(Permission);
}
reader.Close();
}
catch (SqlException)
{
return(PermissionList);
}
finally
{
connection.Close();
}
return(PermissionList);
}
public static List <Architect> List()
{
List <Architect> ArchitectList = new List <Architect>();
SqlConnection connection = PMMSData.GetConnection();
String selectProcedure = "[Project_ArchitectSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
Architect Architect = new Architect();
while (reader.Read())
{
Architect = new Architect();
Architect.ArchitectID = System.Convert.ToInt32(reader["ArchitectID"]);
Architect.ArchitectName = Convert.ToString(reader["ArchitectName"]);
ArchitectList.Add(Architect);
}
reader.Close();
}
catch (SqlException)
{
return(ArchitectList);
}
finally
{
connection.Close();
}
return(ArchitectList);
}
public static bool Update(Reconciliation oldReconciliation,
Reconciliation newReconciliation)
{
SqlConnection connection = PMMSData.GetConnection();
string updateProcedure = "[ReconciliationUpdate]";
SqlCommand updateCommand = new SqlCommand(updateProcedure, connection);
updateCommand.CommandType = CommandType.StoredProcedure;
updateCommand.Parameters.AddWithValue("@NewInvoiceID", newReconciliation.InvoiceID);
updateCommand.Parameters.AddWithValue("@NewPaymentDate", newReconciliation.PaymentDate);
updateCommand.Parameters.AddWithValue("@NewPaymentAmount", newReconciliation.PaymentAmount);
updateCommand.Parameters.AddWithValue("@NewTDSAmount", newReconciliation.TDSAmount);
if (newReconciliation.Remarks != null)
{
updateCommand.Parameters.AddWithValue("@NewRemarks", newReconciliation.Remarks);
}
else
{
updateCommand.Parameters.AddWithValue("@NewRemarks", DBNull.Value);
}
updateCommand.Parameters.AddWithValue("@OldReconciliationID", oldReconciliation.ReconciliationID);
updateCommand.Parameters.AddWithValue("@OldInvoiceID", oldReconciliation.InvoiceID);
updateCommand.Parameters.AddWithValue("@OldPaymentDate", oldReconciliation.PaymentDate);
updateCommand.Parameters.AddWithValue("@OldPaymentAmount", oldReconciliation.PaymentAmount);
updateCommand.Parameters.AddWithValue("@OldTDSAmount", oldReconciliation.TDSAmount);
if (oldReconciliation.Remarks != null)
{
updateCommand.Parameters.AddWithValue("@OldRemarks", oldReconciliation.Remarks);
}
else
{
updateCommand.Parameters.AddWithValue("@OldRemarks", DBNull.Value);
}
updateCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
updateCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
try
{
connection.Open();
updateCommand.ExecuteNonQuery();
int count = System.Convert.ToInt32(updateCommand.Parameters["@ReturnValue"].Value);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static bool Update(TaskAttachment oldTaskAttachment,
TaskAttachment newTaskAttachment)
{
SqlConnection connection = PMMSData.GetConnection();
string updateProcedure = "[TaskAttachmentUpdate]";
SqlCommand updateCommand = new SqlCommand(updateProcedure, connection);
updateCommand.CommandType = CommandType.StoredProcedure;
updateCommand.Parameters.AddWithValue("@NewTaskID", newTaskAttachment.TaskID);
updateCommand.Parameters.AddWithValue("@NewTaskAttachmentID", newTaskAttachment.TaskAttachmentID);
updateCommand.Parameters.AddWithValue("@NewAttachmentName", newTaskAttachment.AttachmentName);
if (newTaskAttachment.Decription != null)
{
updateCommand.Parameters.AddWithValue("@NewDecription", newTaskAttachment.Decription);
}
else
{
updateCommand.Parameters.AddWithValue("@NewDecription", DBNull.Value);
}
updateCommand.Parameters.AddWithValue("@NewFilePath", newTaskAttachment.FilePath);
updateCommand.Parameters.AddWithValue("@OldTaskID", oldTaskAttachment.TaskID);
updateCommand.Parameters.AddWithValue("@OldTaskAttachmentID", oldTaskAttachment.TaskAttachmentID);
updateCommand.Parameters.AddWithValue("@OldAttachmentName", oldTaskAttachment.AttachmentName);
if (oldTaskAttachment.Decription != null)
{
updateCommand.Parameters.AddWithValue("@OldDecription", oldTaskAttachment.Decription);
}
else
{
updateCommand.Parameters.AddWithValue("@OldDecription", DBNull.Value);
}
updateCommand.Parameters.AddWithValue("@OldFilePath", oldTaskAttachment.FilePath);
updateCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
updateCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
try
{
connection.Open();
updateCommand.ExecuteNonQuery();
int count = System.Convert.ToInt32(updateCommand.Parameters["@ReturnValue"].Value);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static DataTable Search(string sField, string sCondition, string sValue)
{
SqlConnection connection = PMMSData.GetConnection();
string selectProcedure = "[UserTypePermissionSearch]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
selectCommand.CommandType = CommandType.StoredProcedure;
if (sField == "User Type I D")
{
selectCommand.Parameters.AddWithValue("@UserTypeName", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@UserTypeName", DBNull.Value);
}
if (sField == "User Type Permission I D")
{
selectCommand.Parameters.AddWithValue("@UserTypePermissionID", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@UserTypePermissionID", DBNull.Value);
}
if (sField == "Permission I D")
{
selectCommand.Parameters.AddWithValue("@PermissionName", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@PermissionName", DBNull.Value);
}
selectCommand.Parameters.AddWithValue("@SearchCondition", sCondition);
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
}
reader.Close();
}
catch (SqlException)
{
return(dt);
}
finally
{
connection.Close();
}
return(dt);
}
public static bool Update(BackupLog oldBackupLog,
BackupLog newBackupLog)
{
SqlConnection connection = PMMSData.GetConnection();
string updateProcedure = "[BackupLogUpdate]";
SqlCommand updateCommand = new SqlCommand(updateProcedure, connection);
updateCommand.CommandType = CommandType.StoredProcedure;
updateCommand.Parameters.AddWithValue("@NewBackupDate", newBackupLog.BackupDate);
updateCommand.Parameters.AddWithValue("@NewFilePath", newBackupLog.FilePath);
if (newBackupLog.Remarks != null)
{
updateCommand.Parameters.AddWithValue("@NewRemarks", newBackupLog.Remarks);
}
else
{
updateCommand.Parameters.AddWithValue("@NewRemarks", DBNull.Value);
}
updateCommand.Parameters.AddWithValue("@OldBackupLogID", oldBackupLog.BackupLogID);
updateCommand.Parameters.AddWithValue("@OldBackupDate", oldBackupLog.BackupDate);
updateCommand.Parameters.AddWithValue("@OldFilePath", oldBackupLog.FilePath);
if (oldBackupLog.Remarks != null)
{
updateCommand.Parameters.AddWithValue("@OldRemarks", oldBackupLog.Remarks);
}
else
{
updateCommand.Parameters.AddWithValue("@OldRemarks", DBNull.Value);
}
updateCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
updateCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
try
{
connection.Open();
updateCommand.ExecuteNonQuery();
int count = System.Convert.ToInt32(updateCommand.Parameters["@ReturnValue"].Value);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static Employee Select_Record(Employee EmployeePara)
{
Employee Employee = new Employee();
SqlConnection connection = PMMSData.GetConnection();
string selectProcedure = "[EmployeeSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@EmployeeID", EmployeePara.EmployeeID);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
Employee.EmployeeID = System.Convert.ToInt32(reader["EmployeeID"]);
Employee.FirstName = System.Convert.ToString(reader["FirstName"]);
Employee.LastName = reader["LastName"] is DBNull ? null : reader["LastName"].ToString();
Employee.DOB = System.Convert.ToDateTime(reader["DOB"]);
Employee.DOJ = System.Convert.ToDateTime(reader["DOJ"]);
Employee.Gender = System.Convert.ToString(reader["Gender"]);
Employee.EMail = reader["EMail"] is DBNull ? null : reader["EMail"].ToString();
Employee.Mobile = System.Convert.ToString(reader["Mobile"]);
Employee.Address1 = reader["Address1"] is DBNull ? null : reader["Address1"].ToString();
Employee.Address2 = reader["Address2"] is DBNull ? null : reader["Address2"].ToString();
Employee.Salary = System.Convert.ToDecimal(reader["Salary"]);
Employee.SignatureURL = reader["SignatureURL"] is DBNull ? null : reader["SignatureURL"].ToString();
Employee.UserName = System.Convert.ToString(reader["UserName"]);
Employee.Password = System.Convert.ToString(reader["Password"]);
Employee.CompanyID = System.Convert.ToInt32(reader["CompanyID"]);
Employee.AddUserID = System.Convert.ToInt32(reader["AddUserID"]);
Employee.AddDate = System.Convert.ToDateTime(reader["AddDate"]);
Employee.ArchiveUserID = reader["ArchiveUserID"] is DBNull ? null : (Int32?)reader["ArchiveUserID"];
Employee.ArchiveDate = reader["ArchiveDate"] is DBNull ? null : (DateTime?)reader["ArchiveDate"];
}
else
{
Employee = null;
}
reader.Close();
}
catch (SqlException)
{
return(Employee);
}
finally
{
connection.Close();
}
return(Employee);
}
public static bool Delete(ArchitectAssociate ArchitectAssociate)
{
SqlConnection connection = PMMSData.GetConnection();
string deleteProcedure = "[ArchitectAssociateDelete]";
SqlCommand deleteCommand = new SqlCommand(deleteProcedure, connection);
deleteCommand.CommandType = CommandType.StoredProcedure;
deleteCommand.Parameters.AddWithValue("@OldArchitectAssociateID", ArchitectAssociate.ArchitectAssociateID);
deleteCommand.Parameters.AddWithValue("@OldArchitectID", ArchitectAssociate.ArchitectID);
deleteCommand.Parameters.AddWithValue("@OldAssociateName", ArchitectAssociate.AssociateName);
if (ArchitectAssociate.ContactNo != null)
{
deleteCommand.Parameters.AddWithValue("@OldContactNo", ArchitectAssociate.ContactNo);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldContactNo", DBNull.Value);
}
if (ArchitectAssociate.EMail != null)
{
deleteCommand.Parameters.AddWithValue("@OldEMail", ArchitectAssociate.EMail);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldEMail", DBNull.Value);
}
deleteCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
deleteCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
try
{
connection.Open();
deleteCommand.ExecuteNonQuery();
int count = System.Convert.ToInt32(deleteCommand.Parameters["@ReturnValue"].Value);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static Invoice Select_Record(Invoice InvoicePara)
{
Invoice Invoice = new Invoice();
SqlConnection connection = PMMSData.GetConnection();
string selectProcedure = "[InvoiceSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@InvoiceID", InvoicePara.InvoiceID);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
Invoice.InvoiceID = System.Convert.ToInt32(reader["InvoiceID"]);
Invoice.InvoiceNo = System.Convert.ToString(reader["InvoiceNo"]);
Invoice.InvoiceDate = System.Convert.ToDateTime(reader["InvoiceDate"]);
Invoice.ProjectID = reader["ProjectID"] is DBNull ? null : (Int32?)reader["ProjectID"];
Invoice.ClientID = System.Convert.ToInt32(reader["ClientID"]);
Invoice.ClientName = reader["ClientName"] is DBNull ? null : reader["ClientName"].ToString();
Invoice.ClientAddress = reader["ClientAddress"] is DBNull ? null : reader["ClientAddress"].ToString();
Invoice.ClientGSTIN = reader["ClientGSTIN"] is DBNull ? null : reader["ClientGSTIN"].ToString();
Invoice.ClientContactNo = reader["ClientContactNo"] is DBNull ? null : reader["ClientContactNo"].ToString();
Invoice.ClientEMail = reader["ClientEMail"] is DBNull ? null : reader["ClientEMail"].ToString();
Invoice.AdditionalDiscount = reader["AdditionalDiscount"] is DBNull ? null : (Decimal?)reader["AdditionalDiscount"];
Invoice.Remarks = reader["Remarks"] is DBNull ? null : reader["Remarks"].ToString();
Invoice.PDFUrl = reader["PDFUrl"] is DBNull ? null : reader["PDFUrl"].ToString();
Invoice.CompanyID = reader["CompanyID"] is DBNull ? null : (Int32?)reader["CompanyID"];
Invoice.AddUserID = reader["AddUserID"] is DBNull ? null : (Int32?)reader["AddUserID"];
Invoice.AddDate = reader["AddDate"] is DBNull ? null : (DateTime?)reader["AddDate"];
Invoice.ArchiveUserID = reader["ArchiveUserID"] is DBNull ? null : (Int32?)reader["ArchiveUserID"];
Invoice.ArchiveDate = reader["ArchiveDate"] is DBNull ? null : (DateTime?)reader["ArchiveDate"];
}
else
{
Invoice = null;
}
reader.Close();
}
catch (SqlException)
{
return(Invoice);
}
finally
{
connection.Close();
}
return(Invoice);
}
public static Company Select_Record(Company CompanyPara)
{
Company Company = new Company();
SqlConnection connection = PMMSData.GetConnection();
string selectProcedure = "[CompanySelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@CompanyID", CompanyPara.CompanyID);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
Company.CompanyID = System.Convert.ToInt32(reader["CompanyID"]);
Company.CompanyName = System.Convert.ToString(reader["CompanyName"]);
Company.Address1 = reader["Address1"] is DBNull ? null : reader["Address1"].ToString();
Company.Address2 = reader["Address2"] is DBNull ? null : reader["Address2"].ToString();
Company.City = reader["City"] is DBNull ? null : reader["City"].ToString();
Company.District = reader["District"] is DBNull ? null : reader["District"].ToString();
Company.State = reader["State"] is DBNull ? null : reader["State"].ToString();
Company.Country = reader["Country"] is DBNull ? null : reader["Country"].ToString();
Company.PinCode = reader["PinCode"] is DBNull ? null : reader["PinCode"].ToString();
Company.ContactNo = reader["ContactNo"] is DBNull ? null : reader["ContactNo"].ToString();
Company.EMail = reader["EMail"] is DBNull ? null : reader["EMail"].ToString();
Company.GSTIN = reader["GSTIN"] is DBNull ? null : reader["GSTIN"].ToString();
Company.InvoiceInitials = reader["InvoiceInitials"] is DBNull ? null : reader["InvoiceInitials"].ToString();
Company.AddUserID = reader["AddUserID"] is DBNull ? null : (Int32?)reader["AddUserID"];
Company.AddDate = reader["AddDate"] is DBNull ? null : (DateTime?)reader["AddDate"];
Company.ArchiveUserID = reader["ArchiveUserID"] is DBNull ? null : (Int32?)reader["ArchiveUserID"];
Company.ArchiveDate = reader["ArchiveDate"] is DBNull ? null : (DateTime?)reader["ArchiveDate"];
}
else
{
Company = null;
}
reader.Close();
}
catch (SqlException)
{
return(Company);
}
finally
{
connection.Close();
}
return(Company);
}
public static bool Delete(AdvancePayment AdvancePayment)
{
SqlConnection connection = PMMSData.GetConnection();
string deleteProcedure = "[AdvancePaymentDelete]";
SqlCommand deleteCommand = new SqlCommand(deleteProcedure, connection);
deleteCommand.CommandType = CommandType.StoredProcedure;
deleteCommand.Parameters.AddWithValue("@OldAdvancePaymentID", AdvancePayment.AdvancePaymentID);
deleteCommand.Parameters.AddWithValue("@OldPaymentDate", AdvancePayment.PaymentDate);
deleteCommand.Parameters.AddWithValue("@OldCompanyID", AdvancePayment.CompanyID);
deleteCommand.Parameters.AddWithValue("@OldClientID", AdvancePayment.ClientID);
deleteCommand.Parameters.AddWithValue("@OldProjectID", AdvancePayment.ProjectID);
deleteCommand.Parameters.AddWithValue("@OldGrossAmount", AdvancePayment.GrossAmount);
deleteCommand.Parameters.AddWithValue("@OldTDSRate", AdvancePayment.TDSRate);
deleteCommand.Parameters.AddWithValue("@OldCGSTRate", AdvancePayment.CGSTRate);
deleteCommand.Parameters.AddWithValue("@OldSGSTRate", AdvancePayment.SGSTRate);
deleteCommand.Parameters.AddWithValue("@OldIGSTRate", AdvancePayment.IGSTRate);
if (AdvancePayment.Remarks != null)
{
deleteCommand.Parameters.AddWithValue("@OldRemarks", AdvancePayment.Remarks);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldRemarks", DBNull.Value);
}
deleteCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
deleteCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
try
{
connection.Open();
deleteCommand.ExecuteNonQuery();
int count = System.Convert.ToInt32(deleteCommand.Parameters["@ReturnValue"].Value);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static Architect Select_Record(Architect ArchitectPara)
{
Architect Architect = new Architect();
SqlConnection connection = PMMSData.GetConnection();
string selectProcedure = "[ArchitectSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@ArchitectID", ArchitectPara.ArchitectID);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
Architect.ArchitectID = System.Convert.ToInt32(reader["ArchitectID"]);
Architect.ArchitectName = System.Convert.ToString(reader["ArchitectName"]);
Architect.Address1 = reader["Address1"] is DBNull ? null : reader["Address1"].ToString();
Architect.Address2 = reader["Address2"] is DBNull ? null : reader["Address2"].ToString();
Architect.City = reader["City"] is DBNull ? null : reader["City"].ToString();
Architect.District = reader["District"] is DBNull ? null : reader["District"].ToString();
Architect.State = reader["State"] is DBNull ? null : reader["State"].ToString();
Architect.Country = reader["Country"] is DBNull ? null : reader["Country"].ToString();
Architect.Pincode = reader["Pincode"] is DBNull ? null : reader["Pincode"].ToString();
Architect.EMail = reader["EMail"] is DBNull ? null : reader["EMail"].ToString();
Architect.ContactNo = reader["ContactNo"] is DBNull ? null : reader["ContactNo"].ToString();
Architect.CompanyID = System.Convert.ToInt32(reader["CompanyID"]);
Architect.AddUserID = reader["AddUserID"] is DBNull ? null : (Int32?)reader["AddUserID"];
Architect.AddDate = reader["AddDate"] is DBNull ? null : (DateTime?)reader["AddDate"];
Architect.ArchiveUserID = reader["ArchiveUserID"] is DBNull ? null : (Int32?)reader["ArchiveUserID"];
Architect.ArchiveDate = reader["ArchiveDate"] is DBNull ? null : (DateTime?)reader["ArchiveDate"];
}
else
{
Architect = null;
}
reader.Close();
}
catch (SqlException)
{
return(Architect);
}
finally
{
connection.Close();
}
return(Architect);
}
public static bool Delete(InvoiceItem InvoiceItem)
{
SqlConnection connection = PMMSData.GetConnection();
string deleteProcedure = "[InvoiceItemDelete]";
SqlCommand deleteCommand = new SqlCommand(deleteProcedure, connection);
deleteCommand.CommandType = CommandType.StoredProcedure;
deleteCommand.Parameters.AddWithValue("@OldInvoiceID", InvoiceItem.InvoiceID);
deleteCommand.Parameters.AddWithValue("@OldInvoiceItemID", InvoiceItem.InvoiceItemID);
if (InvoiceItem.Description != null)
{
deleteCommand.Parameters.AddWithValue("@OldDescription", InvoiceItem.Description);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldDescription", DBNull.Value);
}
deleteCommand.Parameters.AddWithValue("@OldQuantity", InvoiceItem.Quantity);
deleteCommand.Parameters.AddWithValue("@OldRate", InvoiceItem.Rate);
deleteCommand.Parameters.AddWithValue("@OldDiscountAmount", InvoiceItem.DiscountAmount);
deleteCommand.Parameters.AddWithValue("@OldCGSTRate", InvoiceItem.CGSTRate);
deleteCommand.Parameters.AddWithValue("@OldSGSTRate", InvoiceItem.SGSTRate);
deleteCommand.Parameters.AddWithValue("@OldIGSTRate", InvoiceItem.IGSTRate);
deleteCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
deleteCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
try
{
connection.Open();
deleteCommand.ExecuteNonQuery();
int count = System.Convert.ToInt32(deleteCommand.Parameters["@ReturnValue"].Value);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static Project Select_Record(Project ProjectPara)
{
Project Project = new Project();
SqlConnection connection = PMMSData.GetConnection();
string selectProcedure = "[ProjectSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@ProjectID", ProjectPara.ProjectID);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
Project.ProjectID = System.Convert.ToInt32(reader["ProjectID"]);
Project.ProjectName = System.Convert.ToString(reader["ProjectName"]);
Project.BillingName = reader["BillingName"] is DBNull ? null : reader["BillingName"].ToString();
Project.Description = reader["Description"] is DBNull ? null : reader["Description"].ToString();
Project.Location = reader["Location"] is DBNull ? null : reader["Location"].ToString();
Project.StartDate = System.Convert.ToDateTime(reader["StartDate"]);
Project.EndDate = reader["EndDate"] is DBNull ? null : (DateTime?)reader["EndDate"];
Project.ProjectStatusID = System.Convert.ToInt32(reader["ProjectStatusID"]);
Project.ClientID = System.Convert.ToInt32(reader["ClientID"]);
Project.ArchitectID = System.Convert.ToInt32(reader["ArchitectID"]);
Project.CompanyID = reader["CompanyID"] is DBNull ? null : (Int32?)reader["CompanyID"];
Project.AddUserID = System.Convert.ToInt32(reader["AddUserID"]);
Project.AddDate = System.Convert.ToDateTime(reader["AddDate"]);
Project.ArchiveUserID = reader["ArchiveUserID"] is DBNull ? null : (Int32?)reader["ArchiveUserID"];
Project.ArchiveDate = reader["ArchiveDate"] is DBNull ? null : (DateTime?)reader["ArchiveDate"];
}
else
{
Project = null;
}
reader.Close();
}
catch (SqlException)
{
return(Project);
}
finally
{
connection.Close();
}
return(Project);
}
public static bool Delete(Timesheet Timesheet)
{
SqlConnection connection = PMMSData.GetConnection();
string deleteProcedure = "[TimesheetDelete]";
SqlCommand deleteCommand = new SqlCommand(deleteProcedure, connection);
deleteCommand.CommandType = CommandType.StoredProcedure;
deleteCommand.Parameters.AddWithValue("@OldTimesheetID", Timesheet.TimesheetID);
deleteCommand.Parameters.AddWithValue("@OldEmployeeID", Timesheet.EmployeeID);
deleteCommand.Parameters.AddWithValue("@OldProjectID", Timesheet.ProjectID);
deleteCommand.Parameters.AddWithValue("@OldEntryDate", Timesheet.EntryDate);
deleteCommand.Parameters.AddWithValue("@OldStartTime", Timesheet.StartTime);
deleteCommand.Parameters.AddWithValue("@OldEndTime", Timesheet.EndTime);
if (Timesheet.TotTime != null)
{
deleteCommand.Parameters.AddWithValue("@OldTotTime", Timesheet.TotTime);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldTotTime", DBNull.Value);
}
deleteCommand.Parameters.AddWithValue("@OldRemarks", Timesheet.Remarks);
deleteCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
deleteCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
try
{
connection.Open();
deleteCommand.ExecuteNonQuery();
int count = System.Convert.ToInt32(deleteCommand.Parameters["@ReturnValue"].Value);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static AdvancePayment Select_Record(AdvancePayment AdvancePaymentPara)
{
AdvancePayment AdvancePayment = new AdvancePayment();
SqlConnection connection = PMMSData.GetConnection();
string selectProcedure = "[AdvancePaymentSelect]";
SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@AdvancePaymentID", AdvancePaymentPara.AdvancePaymentID);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
AdvancePayment.AdvancePaymentID = System.Convert.ToInt32(reader["AdvancePaymentID"]);
AdvancePayment.PaymentDate = System.Convert.ToDateTime(reader["PaymentDate"]);
AdvancePayment.CompanyID = System.Convert.ToInt32(reader["CompanyID"]);
AdvancePayment.ClientID = System.Convert.ToInt32(reader["ClientID"]);
AdvancePayment.ProjectID = System.Convert.ToInt32(reader["ProjectID"]);
AdvancePayment.GrossAmount = System.Convert.ToDecimal(reader["GrossAmount"]);
AdvancePayment.TDSRate = System.Convert.ToDecimal(reader["TDSRate"]);
AdvancePayment.CGSTRate = System.Convert.ToDecimal(reader["CGSTRate"]);
AdvancePayment.SGSTRate = System.Convert.ToDecimal(reader["SGSTRate"]);
AdvancePayment.IGSTRate = System.Convert.ToDecimal(reader["IGSTRate"]);
AdvancePayment.Remarks = reader["Remarks"] is DBNull ? null : reader["Remarks"].ToString();
}
else
{
AdvancePayment = null;
}
reader.Close();
}
catch (SqlException)
{
return(AdvancePayment);
}
finally
{
connection.Close();
}
return(AdvancePayment);
}
public static bool Add(Reconciliation Reconciliation)
{
SqlConnection connection = PMMSData.GetConnection();
string insertProcedure = "[ReconciliationInsert]";
SqlCommand insertCommand = new SqlCommand(insertProcedure, connection);
insertCommand.CommandType = CommandType.StoredProcedure;
insertCommand.Parameters.AddWithValue("@InvoiceID", Reconciliation.InvoiceID);
insertCommand.Parameters.AddWithValue("@PaymentDate", Reconciliation.PaymentDate);
insertCommand.Parameters.AddWithValue("@PaymentAmount", Reconciliation.PaymentAmount);
insertCommand.Parameters.AddWithValue("@TDSAmount", Reconciliation.TDSAmount);
if (Reconciliation.Remarks != null)
{
insertCommand.Parameters.AddWithValue("@Remarks", Reconciliation.Remarks);
}
else
{
insertCommand.Parameters.AddWithValue("@Remarks", DBNull.Value);
}
insertCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
insertCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
int count = System.Convert.ToInt32(insertCommand.Parameters["@ReturnValue"].Value);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}