public List<FPRole> getRoleByUser(UserAC user, DbTransaction transaction)
{
SqlTransaction trans = (SqlTransaction)transaction;
String sql = "select name, other , FPObject.* from FPRole ,FPObject, UserAC , UserRole where FPRole.ObjectId = FPObject.ObjectId and FPObject.IsDeleted = 0 and UserAC.ObjectID = UserRole.usr and FPRole.ObjectId = UserRole.role and UserAC.ObjectId='"+user.objectId +"'";
SqlConnection conn = trans.Connection;
SqlCommand cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.Transaction = trans;
cmd.CommandText = sql;
List<FPRole> roles = getQueryResult(cmd);
cmd.Dispose();
return roles;
}
public bool add(UserAC user, DbTransaction transaction)
{
IFPObjectDAO fpObjectDAO = DAOFactory.getInstance().createFPObjectDAO();
fpObjectDAO.add(user, transaction);
SqlTransaction trans = (SqlTransaction)transaction;
String sql = "insert into UserAC(ObjectId, chi_name, eng_name, user_name, dept, user_password,post,email,remark,status) values " +
"(@ObjectId, @chi_name, @eng_name, @user_name, @dept, @user_password,@post,@email,@remark,@status)";
SqlConnection conn = trans.Connection;
SqlCommand cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.Transaction = trans;
cmd.CommandText = sql;
int? dept=null ;
if(user.dept !=null )
dept =user.dept.objectId;
int? section = null;
if (user.section != null)
section = user.section.objectId;
cmd.Parameters.Add(genSqlParameter("ObjectId", SqlDbType.Int, 10, user.objectId));
cmd.Parameters.Add(genSqlParameter("chi_name", SqlDbType.NVarChar, 50, user.chi_name));
cmd.Parameters.Add(genSqlParameter("eng_name", SqlDbType.NVarChar, 50, user.eng_name));
cmd.Parameters.Add(genSqlParameter("user_name", SqlDbType.NVarChar, 50, user.user_name));
cmd.Parameters.Add(genSqlParameter("dept",SqlDbType.Int,10,dept));
cmd.Parameters.Add(genSqlParameter("section", SqlDbType.Int, 10, section));
cmd.Parameters.Add(genSqlParameter("user_password",SqlDbType.NVarChar,50,user.user_password));
cmd.Parameters.Add(genSqlParameter("post", SqlDbType.NVarChar, 50, user.post));
cmd.Parameters.Add(genSqlParameter("email", SqlDbType.NVarChar, 50, user.email));
cmd.Parameters.Add(genSqlParameter("remark", SqlDbType.NVarChar, 255, user.remark));
cmd.Parameters.Add(genSqlParameter("status", SqlDbType.NVarChar, 50, user.status));
cmd.ExecuteNonQuery();
updateUserRole(user, transaction);
cmd.Dispose();
return true;
}
public PrintItem getPrintJobByID(string jobid, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IPrintItemDAO printJobDAO = DAOFactory.getInstance().createPrintJobDAO();
PrintItem job = printJobDAO.get(jobid, transaction);
transaction.Commit();
return job;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public Customer getCustomerByID(int objectid, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
ICustomerDAO customerDAO = DAOFactory.getInstance().createCustomerDAO();
Customer c = customerDAO.getByID(objectid, transaction);
transaction.Commit();
return c;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public List<PrintJobCategory> getAllPrintJobCategory(UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IPrintJobCategoryDAO categoryDAO = DAOFactory.getInstance().createPrintJobCategoryDAO();
List<PrintJobCategory> categorys = categoryDAO.search(" where isdeleted = 0 ", transaction);
transaction.Commit();
return categorys;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public bool updateInventory(Inventory inventory, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IInventoryDAO ccDao = DAOFactory.getInstance().createInventoryDAO();
inventory.updateBy = user.eng_name;
inventory.updateDate = DateTime.Now;
ccDao.Update(inventory, transaction);
transaction.Commit();
return true;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public Consumption getconsumption(int objectid, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IConsumptionDAO inventoryDao = DAOFactory.getInstance().createConsumptionDAO();
Consumption consumptions = inventoryDao.get(objectid, transaction);
transaction.Commit();
return consumptions;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public Inventory getInventoryById(int inventoryId, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IInventoryDAO inventoryDao = DAOFactory.getInstance().createInventoryDAO();
Inventory inventory = inventoryDao.Get(inventoryId, transaction);
transaction.Commit();
return inventory;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public List<UserAC> getUserNotInRole(string roleID, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IUserDAO userDao = DAOFactory.getInstance().createUserDAO();
List<UserAC> users = userDao.getUserNotInRole(roleID, transaction);
transaction.Commit();
return users;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public int deliveryCount(string condition, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IDeliveryDAO deliveryDAO = DAOFactory.getInstance().createDeliveryDAO();
int count = deliveryDAO.count(condition, transaction);
transaction.Commit();
return count;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public List<PrintItemDetail> getPrintJobLookupByCategory(string categoryCode, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IPrintItemDetailDAO lookupDAO = DAOFactory.getInstance().createPrintJobLookupDAO();
List<PrintItemDetail> lookups = lookupDAO.search(" where category = '" + categoryCode + "' and code_desc is not null order by ordering asc", transaction);
transaction.Commit();
return lookups;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public UserAC getUserByID(int uid, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IUserDAO userDAO = DAOFactory.getInstance().createUserDAO();
UserAC tuser = userDAO.get(uid, transaction);
transaction.Commit();
return tuser;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public PrintOrder getPrintOrderByID(string pid, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IPrintOrderDAO PrintOrderDAO = DAOFactory.getInstance().createPrintOrderDAO();
PrintOrder order = PrintOrderDAO.get(pid, transaction);
transaction.Commit();
return order;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public AssetConsumption getPrintJobDetailByID(string id, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IAssetConsumptionDAO acDAO = DAOFactory.getInstance().createPrintJobDetailDAO();
List<AssetConsumption> jobs = acDAO.search(" where IsDeleted = 0 and FPObject.ObjectId = " + id, transaction);
transaction.Commit();
if (jobs.Count > 0)
return jobs[0];
else
return null;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public PrintJobCategory getPrintJobCategoryByID(string id, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IPrintJobCategoryDAO categoryDAO = DAOFactory.getInstance().createPrintJobCategoryDAO();
PrintJobCategory category = categoryDAO.get(id, transaction);
transaction.Commit();
return category;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public bool deleteConsumption(Consumption consumption, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IConsumptionDAO ccDao = DAOFactory.getInstance().createConsumptionDAO();
consumption.updateBy = user.eng_name;
consumption.updateDate = DateTime.Now;
consumption.isDeleted = false;
ccDao.delete(consumption, transaction);
transaction.Commit();
return true;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public bool addConsumption(Consumption consumption, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IConsumptionDAO inventoryDao = DAOFactory.getInstance().createConsumptionDAO();
ISequenceDAO seqDAO = DAOFactory.getInstance().createSequenceDAO();
consumption.objectId = seqDAO.getNextObjectId(transaction);
consumption.updateBy = user.eng_name;
consumption.createDate = DateTime.Now;
consumption.updateDate = DateTime.Now;
consumption.isDeleted = false;
inventoryDao.add(consumption, transaction);
transaction.Commit();
return true;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public List<String> getCategoryItemCodesByCategory(PrintJobCategory category, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IPrintItemDetailDAO lookupDAO = DAOFactory.getInstance().createPrintJobLookupDAO();
List<String> items = lookupDAO.getItemNamesByCategoryId(category.id, transaction);
transaction.Commit();
return items;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public List<UserAC> getSales(string query, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IUserDAO userDAO = DAOFactory.getInstance().createUserDAO();
List<UserAC> users = userDAO.search(" where isdeleted = 0 "+query, transaction);
transaction.Commit();
return users;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public List<PrintItem> getAllJob(String query, int limit, int start, String sort, bool descending, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IPrintItemDAO printJobDAO = DAOFactory.getInstance().createPrintJobDAO();
List<PrintItem> jobs = printJobDAO.search(query, limit, start, sort, descending, transaction);
transaction.Commit();
return jobs;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
//role
public List<FPRole> getRoles(string query, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IFPRoleDAO roleDao = DAOFactory.getInstance().createFPRoleDAO();
List<FPRole> roles = roleDao.search(" where isdeleted = 0 " + query, transaction);
transaction.Commit();
return roles;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public List<PrintItem> getPrintJobByOrder(PrintOrder order, UserAC user)
{
if (order == null)
return new List<PrintItem>();
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IPrintItemDAO printJobDAO = DAOFactory.getInstance().createPrintJobDAO();
List<PrintItem> jobs = printJobDAO.search("where pid='" + order.pid + "' and isdeleted = 0 ", 1000, 0, "jobid", true, transaction);
transaction.Commit();
return jobs;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
//Inventory
public List<Inventory> getInventories(string query,int limit, int start, string sort, bool descending, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IInventoryDAO inventoryDao = DAOFactory.getInstance().createInventoryDAO();
List<Inventory> inventories = inventoryDao.List(" where isdeleted = 0 "+query , limit, start, sort, descending, transaction);
transaction.Commit();
return inventories;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public List<CustomerContact> getContactsByCode(string code, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
ICustomerContactDAO customercontactDAO = DAOFactory.getInstance().createCustomerContactDAO();
List<CustomerContact> customercontacts = customercontactDAO.search(" and cid='" + code + "'", transaction);
transaction.Commit();
return customercontacts;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public int inventoryCount(string query, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IInventoryDAO inventoryDao = DAOFactory.getInstance().createInventoryDAO();
int count = inventoryDao.count(" where isDeleted=0 " + query, transaction);
transaction.Commit();
return count;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public bool addDelivery(Delivery delivery, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IDeliveryDAO ccDao = DAOFactory.getInstance().createDeliveryDAO();
ISequenceDAO seqDAO = DAOFactory.getInstance().createSequenceDAO();
delivery.objectId = seqDAO.getNextObjectId(transaction);
delivery.number = "DAA" + delivery.objectId;
delivery.updateBy = user.eng_name;
delivery.createDate = DateTime.Now;
delivery.updateDate = DateTime.Now;
delivery.isDeleted = false;
ccDao.Add(delivery, transaction);
transaction.Commit();
return true;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
//consumption
public List<Consumption> getConsumptions(string query, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IConsumptionDAO inventoryDao = DAOFactory.getInstance().createConsumptionDAO();
List<Consumption> consumptions = inventoryDao.search(" where isdeleted = 0 " + query, transaction);
transaction.Commit();
return consumptions;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public List<Customer> getAllCustomer(int limit, int start, String sort, bool descending, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
ICustomerDAO customerDAO = DAOFactory.getInstance().createCustomerDAO();
List<Customer> customers = customerDAO.search(" where isdeleted = 0 ", limit, start, sort, descending, transaction);
transaction.Commit();
return customers;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public CustomerContact getCustomerContactByCustomer(Customer customer, string ctype, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
ICustomerContactDAO customercontactDAO = DAOFactory.getInstance().createCustomerContactDAO();
CustomerContact cc = customercontactDAO.getCustomerContactByCustomer(customer, ctype, transaction);
transaction.Commit();
return cc;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
public int addInventory(Inventory inventory, UserAC user)
{
IDatabase db = DAOFactory.getInstance().getDatabase();
DbConnection conn = db.getConnection();
DbTransaction transaction = db.beginTransaction(conn);
try
{
IInventoryDAO inventoryDao = DAOFactory.getInstance().createInventoryDAO();
ISequenceDAO seqDAO = DAOFactory.getInstance().createSequenceDAO();
inventory.objectId = seqDAO.getNextObjectId(transaction);
inventory.assetno = "AAA" + inventory.objectId;
inventory.updateBy = user.eng_name;
inventory.createDate = DateTime.Now;
inventory.updateDate = DateTime.Now;
inventory.isDeleted = false;
inventoryDao.Add(inventory, transaction);
transaction.Commit();
return inventory.objectId;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
}
}
