public Holiday GetData(int id)
{
SQLConnector.OpenConnection();
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.CommandText = string.Format("SELECT * FROM Holiday WHERE ID ={0}", id);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
Holiday item = new Holiday();
if (reader.Read())
{
item.IsNew = false;
item.ID = Convert.ToInt32(reader["ID"]);
item.Code = (reader["Code"].ToString());
item.Description = (reader["Description"].ToString());
item.FromDate = Convert.ToDateTime(reader["FromDate"]);
item.ToDate = Convert.ToDateTime(reader["ToDate"]);
}
reader.Close();
reader.Dispose();
reader = null;
SQLConnector.CloseConnection();
return(item);
}
public Invoice GetData(int id)
{
SQLConnector.OpenConnection();
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.CommandText = string.Format("SELECT * FROM [Invoice] WHERE ID ={0}", id);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
Invoice item = new Invoice();
if (reader.Read())
{
item.IsNew = false;
item.ID = Convert.ToInt32(reader["ID"]);
item.UserName = (reader["UserName"].ToString());
item.InvoiceReference = (reader["InvoiceReference"]).ToString();
item.InvoiceValue = Convert.ToInt32(reader["InvoiceValue"]);
item.POReference = (reader["POReference"].ToString());
item.InvoiceDate = Convert.ToDateTime(reader["InvoiceDate"]);
item.OmniflowReference = (reader["OmniflowReference"]).ToString();
item.ReceiveDate = Convert.ToDateTime(reader["ReceiveDate"]);
item.PaymentdueDate = Convert.ToDateTime(reader["PaymentdueDate"]);
item.Status = (reader["Status"]).ToString();
}
reader.Close();
reader.Dispose();
reader = null;
SQLConnector.CloseConnection();
return(item);
}
public void Delete(int id)
{
if (Convert.ToInt32(id) <= 0)
{
throw new Exception("ID is required.");
}
try
{
SQLConnector.OpenConnection();
SQLConnector.BeginTransaction();
string sql = string.Format("DELETE FROM Vendor WHERE ID ={0}", id);
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.Transaction = SQLConnector.Transaction;
command.CommandText = sql;
command.CommandType = System.Data.CommandType.Text;
command.ExecuteNonQuery();
SQLConnector.CommitTransaction();
SQLConnector.CloseConnection();
}
catch (Exception exp)
{
SQLConnector.RollbackTransaction();
throw exp;
}
}
public static int GenerateMaxNumber(string tableName, string columnName, string whereClause)
{
string sql = string.Format("SELECT MAX({0}) FROM {1} {2}", columnName, tableName, whereClause);
SQLConnector.OpenConnection();
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.Transaction = SQLConnector.Transaction;
command.CommandText = sql;
command.CommandType = System.Data.CommandType.Text;
object maxID = command.ExecuteScalar();
if (maxID == DBNull.Value)
{
maxID = 1;
}
else
{
maxID = Convert.ToInt32(maxID) + 1;
}
//SQLConnector.CloseConnection();
return(Convert.ToInt32(maxID));
}
public static List <Document> GetAllData(string sortColumn, string sortDirection)
{
SQLConnector.OpenConnection();
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.CommandText = string.Format("SELECT * FROM Document ORDER BY {0} {1}", sortColumn, sortDirection);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
List <Document> items = new List <Document>();
while (reader.Read())
{
Document item = new Document();
item.IsNew = false;
item.ID = Convert.ToInt32(reader["ID"]);
item.Description = (reader["Description"].ToString());
item.IsMendatory = Convert.ToBoolean(reader["IsMendatory"]);
items.Add(item);
}
reader.Close();
reader.Dispose();
reader = null;
SQLConnector.CloseConnection();
return(items);
}
public static List <Holiday> GetAllData(string sortColumn, string sortDirection)
{
SQLConnector.OpenConnection();
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.CommandText = string.Format("SELECT * FROM Holiday ORDER BY {0} {1}", sortColumn, sortDirection);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
List <Holiday> items = new List <Holiday>();
while (reader.Read())
{
Holiday item = new Holiday();
item.IsNew = false;
item.ID = Convert.ToInt32(reader["ID"]);
item.Code = (reader["Code"].ToString());
item.Description = (reader["Description"].ToString());
item.FromDate = Convert.ToDateTime(reader["FromDate"]);
item.ToDate = Convert.ToDateTime(reader["ToDate"]);
items.Add(item);
}
reader.Close();
reader.Dispose();
reader = null;
SQLConnector.CloseConnection();
return(items);
}
public Document GetData(int id)
{
SQLConnector.OpenConnection();
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.CommandText = string.Format("SELECT * FROM Document WHERE ID ={0}", id);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
Document item = new Document();
if (reader.Read())
{
item.IsNew = false;
item.ID = Convert.ToInt32(reader["ID"]);
item.Description = (reader["Description"].ToString());
item.IsMendatory = Convert.ToBoolean(reader["IsMendatory"]);
}
reader.Close();
reader.Dispose();
reader = null;
SQLConnector.CloseConnection();
return(item);
}
public static List <User> GetAllData(string sortColumn, string sortDirection)
{
SQLConnector.OpenConnection();
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.CommandText = string.Format("SELECT * FROM [User] ORDER BY {0} {1}", sortColumn, sortDirection);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
List <User> items = new List <User>();
while (reader.Read())
{
User item = new User();
item.IsNew = false;
item.ID = Convert.ToInt32(reader["ID"]);
item.UserName = Convert.ToString(reader["UserName"]);
item.UserID = Convert.ToString(reader["UserID"]);
item.Password = (reader["Password"].ToString());
item.IsAdmin = Convert.ToBoolean(reader["IsAdmin"]);
item.IsOperator = Convert.ToBoolean(reader["IsOperator"]);
items.Add(item);
}
reader.Close();
reader.Dispose();
reader = null;
SQLConnector.CloseConnection();
return(items);
}
public User GetData(string userID)
{
SQLConnector.OpenConnection();
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.CommandText = string.Format("SELECT * FROM [User] WHERE UserID ='{0}'", userID);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
User item = new User();
if (reader.Read())
{
item.IsNew = false;
item = new User();
item.ID = Convert.ToInt32(reader["ID"]);
item.UserName = Convert.ToString(reader["UserName"]);
item.UserID = Convert.ToString(reader["UserID"]);
item.Password = (reader["Password"].ToString());
item.IsAdmin = Convert.ToBoolean(reader["IsAdmin"]);
item.IsOperator = Convert.ToBoolean(reader["IsOperator"]);
}
reader.Close();
reader.Dispose();
reader = null;
SQLConnector.CloseConnection();
return(item);
}
public void Save()
{
if (this.Code.Length == 0)
{
throw new Exception("Code is required");
}
if (Global.IsExists("Vendor", "ID", "Code", this.ID, this.Code))
{
throw new Exception("Your entered Code is already exists here. Please enter your valid Code.");
}
if (_email.Length == 0)
{
throw new Exception("Email is required");
}
if (_userName.Length == 0)
{
throw new Exception("UserName is required");
}
try
{
SQLConnector.OpenConnection();
SQLConnector.BeginTransaction();
string sql = string.Empty;
if (_isNew)
{
_id = Global.GenerateMaxNumber("Vendor", "ID");
sql = string.Format("INSERT INTO Vendor(ID,UserName,Code,Address,Email,ContactNo,BankName,AccountNo,PaymentTerm,EntryUserId,UpdateUserId,UpdateDate,isActive) VALUES({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8},'{9}','{10}','{11}',{12})", _id, _userName, _code, _address, _email, _contactNo, _bankName, _accountNo, _paymentTerm, _entryuserId, _updateuserId, _updateDate, Convert.ToInt32(_isActive));
}
else
{
sql = string.Format("UPDATE Vendor SET UserName = '******', Code='{1}',Address = '{2}', Email= '{3}', ContactNo= '{4}',BankName = '{5}',AccountNo = '{6}',PaymentTerm = {7},EntryUserId = '{8}',UpdateUserId = '{9}',UpdateDate = '{10}',IsActive = {11} WHERE ID = {12} ", _userName, _code, _address, _email, _contactNo, _bankName, _accountNo, _paymentTerm, _entryuserId, _updateuserId, _updateDate, Convert.ToInt32(_isActive), _id);
}
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.Transaction = SQLConnector.Transaction;
command.CommandText = sql;
command.CommandType = System.Data.CommandType.Text;
command.ExecuteNonQuery();
SQLConnector.CommitTransaction();
SQLConnector.CloseConnection();
}
catch (Exception exp)
{
SQLConnector.RollbackTransaction();
throw exp;
}
}
public void Save()
{
if (this.UserName.Length == 0)
{
throw new Exception("UserName is required");
}
if (Global.IsExists("Report", "ID", "UserName", this.ID, this.UserName))
{
throw new Exception("Your entered UserName is already exists here. Please enter your valid UserName.");
}
if (_invoiceReference.Length == 0)
{
throw new Exception("Invoice Reference is required");
}
if (_omniflowReference.Length == 0)
{
throw new Exception("Omniflow Reference is required");
}
try
{
SQLConnector.OpenConnection();
SQLConnector.BeginTransaction();
string sql = string.Empty;
if (_isNew)
{
_id = Global.GenerateMaxNumber("Report", "ID");
sql = string.Format("INSERT INTO Report(ID,Code,UserName,InvoiceReference,InvoiceDate,POReference,InvoiceValue,OmniflowReference,ReceiveDate,PaymentTerm,PaymentdueDate,PaymentDate,PaidAmount,TaxDeduction,VatDeduction,Status) VALUES({0},'{1}','{2}',{3},'{4}','{5}',{6},{7},'{8}',{9},'{10}','{11}','{12}','{13}','{14}','{15}')", _id, _code, _userName, _invoiceReference, _invoiceDate, _poReference, _invoiceValue, _omniflowReference, _receiveDate, _paymentTerm, _paymentdueDate, _paymentDate, _paidAmount, _taxDeduction, _vatDeduction, _status);
}
else
{
sql = string.Format("UPDATE Report SET Code ='{0}',UserName='******',InvoiceReference = '{2}',InvoiceDate = '{3}',POReference = '{4}',InvoiceValue = {5},OmniflowReference = '{6}',ReceiveDate = '{7}',PaymentTerm = {8},PaymentdueDate = '{9}',PaymentDate = '{10}',PaidAmount = '{11}',TaxDeduction = '{12}',VatDeduction = '{13}' ,Status = '{14}' WHERE ID = {15} ", _code, _userName, _invoiceReference, _invoiceDate, _poReference, _invoiceValue, _omniflowReference, _receiveDate, _paymentTerm, _paymentdueDate, _paymentDate, _paidAmount, _taxDeduction, _vatDeduction, _status, _id);
}
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.Transaction = SQLConnector.Transaction;
command.CommandText = sql;
command.CommandType = System.Data.CommandType.Text;
command.ExecuteNonQuery();
SQLConnector.CommitTransaction();
SQLConnector.CloseConnection();
}
catch (Exception exp)
{
SQLConnector.RollbackTransaction();
throw exp;
}
}
public static List <Report> GetAllData(string status, string sortColumn, string sortDirection)
{
string whereClause = string.Empty;
if (status.Length > 0)
{
whereClause = string.Format("WHERE Status = '{0}'", status);
}
SQLConnector.OpenConnection();
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.CommandText = string.Format("SELECT * FROM Report {0} ORDER BY {1} {2}", whereClause, sortColumn, sortDirection);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
List <Report> items = new List <Report>();
while (reader.Read())
{
Report item = new Report();
item.IsNew = false;
item.ID = Convert.ToInt32(reader["ID"]);
item.Code = (reader["Code"].ToString());
item.UserName = (reader["UserName"].ToString());
item.InvoiceReference = (reader["InvoiceReference"].ToString());
item.InvoiceDate = Convert.ToDateTime(reader["InvoiceDate"]);
item.POReference = (reader["POReference"].ToString());
item.InvoiceValue = Convert.ToInt32(reader["InvoiceValue"]);
item.OmniflowReference = (reader["OmniflowReference"]).ToString();
item.ReceiveDate = Convert.ToDateTime(reader["ReceiveDate"]);
item.PaymentTerm = Convert.ToInt32(reader["PaymentTerm"]);
item.PaymentdueDate = Convert.ToDateTime(reader["PaymentdueDate"]);
item.PaymentDate = Convert.ToDateTime(reader["PaymentDate"]);
item.PaidAmount = (reader["PaidAmount "].ToString());
item.TaxDeduction = (reader["TaxDeduction"].ToString());
item.VatDeduction = (reader["VatDeduction"].ToString());
item.Status = (reader["Status"]).ToString();
items.Add(item);
}
reader.Close();
reader.Dispose();
reader = null;
SQLConnector.CloseConnection();
return(items);
}
public void Save()
{
if (this.UserName.Length == 0)
{
throw new Exception("User Name is required");
}
if (Global.IsExists("[User]", "ID", "UserName", this.ID, this.UserName))
{
throw new Exception("Your entered User ID or Password is wrong. Please enter your valid User ID or Password.");
}
if (_password.Length == 0)
{
throw new Exception("Password is required");
}
try
{
SQLConnector.OpenConnection();
SQLConnector.BeginTransaction();
string sql = string.Empty;
if (_isNew)
{
_id = Global.GenerateMaxNumber("[User]", "ID");
sql = string.Format("INSERT INTO [User](ID,UserName,UserID,Password,IsAdmin,IsOperator) VALUES({0},'{1}','{2}','{3}',{4},{5})", _id, _userName, _userID, _password, Convert.ToInt32(_isAdmin), Convert.ToInt32(_isOperator));
}
else
{
sql = string.Format("UPDATE [User] SET UserName = '******', UserID = '{1}', Password = '******', IsAdmin = {3}, IsOperator = {4} WHERE ID = {5} ", _userName, _userID, _password, Convert.ToInt32(_isAdmin), Convert.ToInt32(_isOperator), _id);
}
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.Transaction = SQLConnector.Transaction;
command.CommandText = sql;
command.CommandType = System.Data.CommandType.Text;
command.ExecuteNonQuery();
SQLConnector.CommitTransaction();
SQLConnector.CloseConnection();
}
catch (Exception exp)
{
SQLConnector.RollbackTransaction();
throw exp;
}
}
public void Save()
{
if (this.Code.Length == 0)
{
throw new Exception("Code is required");
}
if (Global.IsExists("Holiday", "ID", "Code", this.ID, this.Code))
{
throw new Exception("Your entered Code is already exists here. Please enter your valid Code.");
}
if (_description.Length == 0)
{
throw new Exception("Description is required");
}
try
{
SQLConnector.OpenConnection();
SQLConnector.BeginTransaction();
string sql = string.Empty;
if (_isNew)
{
_id = Global.GenerateMaxNumber("Holiday", "ID");
sql = string.Format("INSERT INTO Holiday(ID,Code,Description,FromDate,ToDate) VALUES({0},'{1}','{2}','{3}','{4}')", _id, _code, _description, _FromDate, _ToDate);
}
else
{
sql = string.Format("UPDATE Holiday SET Code = '{0}', Description = '{1}', FromDate = '{2}', ToDate = '{3}' WHERE ID = {4} ", _code, _description, _FromDate, _ToDate, _id);
}
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.Transaction = SQLConnector.Transaction;
command.CommandText = sql;
command.CommandType = System.Data.CommandType.Text;
command.ExecuteNonQuery();
SQLConnector.CommitTransaction();
SQLConnector.CloseConnection();
}
catch (Exception exp)
{
SQLConnector.RollbackTransaction();
throw exp;
}
}
public void Save()
{
if (this.Description.Length == 0)
{
throw new Exception("Description is required");
}
if (Global.IsExists("Document", "ID", "Description", this.ID, this.Description))
{
throw new Exception("Your entered Description is already exists here. Please enter your valid Description.");
}
try
{
SQLConnector.OpenConnection();
SQLConnector.BeginTransaction();
string sql = string.Empty;
if (_isNew)
{
_id = Global.GenerateMaxNumber("Document", "ID");
sql = string.Format("INSERT INTO Document(ID,Description,isMendatory) VALUES({0},'{1}',{2})", _id, _description, Convert.ToInt32(_isMendatory));
}
else
{
sql = string.Format("UPDATE Document SET Description = '{0}', IsMendatory = {1} WHERE ID = {2} ", _description, Convert.ToInt32(_isMendatory), _id);
}
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.Transaction = SQLConnector.Transaction;
command.CommandText = sql;
command.CommandType = System.Data.CommandType.Text;
command.ExecuteNonQuery();
SQLConnector.CommitTransaction();
SQLConnector.CloseConnection();
}
catch (Exception exp)
{
SQLConnector.RollbackTransaction();
throw exp;
}
}
public static List <Vendor> GetAllData(string sortColumn, string sortDirection)
{
SQLConnector.OpenConnection();
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.CommandText = string.Format("SELECT * FROM Vendor ORDER BY {0} {1}", sortColumn, sortDirection);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
List <Vendor> items = new List <Vendor>();
while (reader.Read())
{
Vendor item = new Vendor();
item.IsNew = false;
item.ID = Convert.ToInt32(reader["ID"]);
item.UserName = (reader["UserName"].ToString());
item.Code = (reader["Code"].ToString());
item.Email = (reader["Email"]).ToString();
item.Address = (reader["Address"]).ToString();
item.ContactNo = (reader["ContactNo"]).ToString();
item.BankName = (reader["BankName"]).ToString();
item.AccountNo = (reader["AccountNo"]).ToString();
item.PaymentTerm = Convert.ToInt32(reader["PaymentTerm"]);
item.IsActive = Convert.ToBoolean(reader["IsActive"]);
item.EntryuserId = (reader["EntryUserID"].ToString());
item.UpdateuserId = (reader["UpdateUserID"].ToString());
item.UpdateDate = Convert.ToDateTime(reader["UpdateDate"]);
items.Add(item);
}
reader.Close();
reader.Dispose();
reader = null;
SQLConnector.CloseConnection();
return(items);
}
public static bool IsExists(string tableName, string idColumnName, string duplicateColumnName, int idColumnValue, string duplicateColumnValue)
{
string sql = string.Format("SELECT COUNT({0}) FROM {1} WHERE {0} <> {2} AND {3} = '{4}'", idColumnName, tableName, idColumnValue, duplicateColumnName, duplicateColumnValue);
SQLConnector.OpenConnection();
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.Transaction = SQLConnector.Transaction;
command.CommandText = sql;
command.CommandType = System.Data.CommandType.Text;
object duplicateValue = command.ExecuteScalar();
if (Convert.ToInt32(duplicateValue) == 0)
{
return(false);
}
else
{
SQLConnector.CloseConnection();
return(true);
}
}
public Vendor GetData(int id)
{
SQLConnector.OpenConnection();
SqlCommand command = new SqlCommand();
command.Connection = SQLConnector.Connection;
command.CommandText = string.Format("SELECT * FROM Vendor WHERE ID ={0}", id);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
Vendor item = new Vendor();
if (reader.Read())
{
item.IsNew = false;
item.ID = Convert.ToInt32(reader["ID"]);
item.UserName = (reader["UserName"].ToString());
item.Code = (reader["Code"].ToString());
item.Email = (reader["Email"]).ToString();
item.Address = (reader["Address"]).ToString();
item.ContactNo = (reader["ContactNo"]).ToString();
item.BankName = (reader["BankName"]).ToString();
item.AccountNo = (reader["AccountNo"]).ToString();
item.PaymentTerm = Convert.ToInt32(reader["PaymentTerm"]);
item.IsActive = Convert.ToBoolean(reader["IsActive"]);
item.EntryuserId = (reader["EntryUserID"].ToString());
item.UpdateuserId = (reader["UpdateUserID"].ToString());
item.UpdateDate = Convert.ToDateTime(reader["UpdateDate"]);
}
reader.Close();
reader.Dispose();
reader = null;
SQLConnector.CloseConnection();
return(item);
}