/**
* Get invoices to enter data
* @return Aps[]
*/
public static function findAPToEntry()
{
$queryString = $_SESSION['last_ap_to_entry_search']['query'];
$options = $_SESSION['last_ap_to_entry_search']['options'];
$condition = new CDbCriteria();
$condition->join = "LEFT JOIN documents ON documents.Document_ID=t.Document_ID
LEFT OUTER JOIN images ON images.Document_ID=t.Document_ID";
$condition->condition = "t.Invoice_Number = '0'";
$condition->addCondition("t.Vendor_ID='0'", 'OR');
$condition->addCondition("t.AP_Approval_Value='0'", 'OR');
$condition->addCondition("t.AP_Approval_Value!='100'", 'AND'); //this condition for Voided APs - allows hide them from DataEntry
if (Yii::app()->user->userType == UsersClientList::PROCESSOR || Yii::app()->user->userType == UsersClientList::APPROVER
|| Yii::app()->user->userType == UsersClientList::CLIENT_ADMIN) {
$condition->addCondition("documents.Client_ID='" . Yii::app()->user->clientID . "'");
}
if (Yii::app()->user->userType == UsersClientList::PROCESSOR || ((Yii::app()->user->userType == UsersClientList::APPROVER
|| Yii::app()->user->userType == UsersClientList::CLIENT_ADMIN) && is_numeric(Yii::app()->user->projectID))) {
$condition->addCondition("documents.Project_ID='" . Yii::app()->user->projectID . "'");
}
if (Yii::app()->user->userType == UsersClientList::USER
&& is_numeric(Yii::app()->user->projectID)) {
$condition->addCondition("documents.Project_ID='" . Yii::app()->user->projectID . "'");
$condition->addCondition("documents.User_ID='" . Yii::app()->user->userID . "'");
}
if (Yii::app()->user->userType == Users::DATA_ENTRY_CLERK) {
//condition to allow DEC see only documents of clients that he has access
$cli_array = Clients::getClientsIDList(Yii::app()->user->userID);
$condition->addInCondition('documents.Client_ID', $cli_array);
}
$condition->addCondition("images.Image_ID IS NOT NULL");
$condition->order = "documents.Created ASC";
$countCond = 0;
if (count($options) > 0 && trim($queryString) != '') {
$search_condition = new CDbCriteria();
$search_condition->join = 'LEFT JOIN vendors ON t.Vendor_ID = vendors.Vendor_ID ';
$search_condition->join .= 'LEFT JOIN companies ON vendors.Vendor_Client_ID = companies.Company_ID ';
if ($options['search_option_com_name']) {
$search_condition->compare('companies.Company_Name', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_fed_id']) {
$search_condition->compare('companies.Company_Fed_ID', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_invoice_num']) {
$search_condition->compare('t.Invoice_Number', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_inv_date']) {
$search_condition->compare('t.Invoice_Date', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_inv_due_date']) {
$search_condition->compare('t.Invoice_Due_Date', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_amount']) {
$search_condition->compare('t.Invoice_Amount', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_description']) {
$search_condition->compare('t.Invoice_Reference', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_1099_type']) {
$search_condition->compare('t.Detail_1099_Box_Number', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_po_number']) {
$search_condition->compare('t.AP_Backup_Document_ID', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_4digits']) {
$search_condition->compare('t.PO_Card_Last_4_Digits', $queryString, true, 'OR');
$countCond++;
}
}
if( $countCond > 0 ) $condition->mergeWith($search_condition);
$aps = Aps::model()->findAll($condition);
return $aps;
}
/**
* Find ARs to entry data
*/
public static function findARsToEntry()
{
$queryString = $_SESSION['last_ar_to_entry_search']['query'];
$options = $_SESSION['last_ar_to_entry_search']['options'];
$condition = new CDbCriteria();
$condition->join = "LEFT JOIN documents ON documents.Document_ID=t.Document_ID";
$condition->condition = "t.Invoice_Number = '0'";
$countCond = 0;
if (count($options) > 0 && trim($queryString) != '') {
$search_condition = new CDbCriteria();
if ($options['search_option_com_name']) {
$search_condition->compare('t.Company_Name', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_inv_num']) {
$search_condition->compare('t.Invoice_Number', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_inv_date']) {
$search_condition->compare('t.Invoice_Date', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_inv_amount']) {
$search_condition->compare('t.Invoice_Amount', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_descr']) {
$search_condition->compare('t.Description', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_terms']) {
$search_condition->compare('t.Terms', $queryString, true, 'OR');
$countCond++;
}
}
if (Yii::app()->user->userType == UsersClientList::PROCESSOR || Yii::app()->user->userType == UsersClientList::APPROVER
|| Yii::app()->user->userType == UsersClientList::CLIENT_ADMIN) {
$condition->addCondition("documents.Client_ID='" . Yii::app()->user->clientID . "'");
}
if (Yii::app()->user->userType == UsersClientList::PROCESSOR || ((Yii::app()->user->userType == UsersClientList::APPROVER
|| Yii::app()->user->userType == UsersClientList::CLIENT_ADMIN) && is_numeric(Yii::app()->user->projectID))) {
$condition->addCondition("documents.Project_ID='" . Yii::app()->user->projectID . "'");
}
if (Yii::app()->user->userType == UsersClientList::USER
&& is_numeric(Yii::app()->user->projectID)) {
$condition->addCondition("documents.Project_ID='" . Yii::app()->user->projectID . "'");
$condition->addCondition("documents.User_ID='" . Yii::app()->user->userID . "'");
}
if (Yii::app()->user->userType == Users::DATA_ENTRY_CLERK) {
//adding condition to allow DEC see only documents of clients that he has access
$cli_array = Clients::getClientsIDList(Yii::app()->user->userID);
$condition->addInCondition('documents.Client_ID', $cli_array);
}
$condition->order = "documents.Created ASC";
if( $countCond > 0 ) $condition->mergeWith($search_condition);
$ars = Ars::model()->findAll($condition);
return $ars;
}
/**
* Function resets values of client and project to the first found for given user
* @param $user_id
*/
public static function resetClientProjectToFirst ($user_id) {
//find companies(clients) available for user
$users_clients = Clients::getClientsIDList($user_id);
//set up session variable to the first client in the list
$_SESSION['last_client'] = $users_clients[0];
//find projects of client
$projects = Projects::getUserProjects($user_id, $users_clients[0]);
$_SESSION['last_project'] = $projects[0]->Project_ID;
}
/**
* Find PCs to entry data
*/
public static function findPayrollsToEntry()
{
$queryString = $_SESSION['last_payr_to_entry_search']['query'];
$options = $_SESSION['last_payr_to_entry_search']['options'];
$condition = new CDbCriteria();
$condition->join = "inner JOIN documents ON documents.Document_ID=t.Document_ID";
$condition->condition = "t.Payroll_Type_ID = '0'";
$condition->addCondition("t.Week_Ending is null", 'OR');
$countCond = 0;
if (count($options) > 0 && trim($queryString) != '') {
$search_condition = new CDbCriteria();
$search_condition->join = 'LEFT JOIN payroll_types ON t.Payroll_Type_ID = payroll_types.Payroll_Type_ID ';
if ($options['search_option_week_end']) {
$search_condition->compare('t.Week_Ending', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_type']) {
$search_condition->compare('payroll_types.Title', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_submitted']) {
$search_condition->compare('t.Submitted', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_version']) {
$search_condition->compare('t.Version', $queryString, true, 'OR');
$countCond++;
}
}
if (Yii::app()->user->userType == UsersClientList::PROCESSOR || Yii::app()->user->userType == UsersClientList::APPROVER
|| Yii::app()->user->userType == UsersClientList::CLIENT_ADMIN) {
$condition->addCondition("documents.Client_ID='" . Yii::app()->user->clientID . "'");
}
if (Yii::app()->user->userType == UsersClientList::PROCESSOR || ((Yii::app()->user->userType == UsersClientList::APPROVER
|| Yii::app()->user->userType == UsersClientList::CLIENT_ADMIN) && is_numeric(Yii::app()->user->projectID))) {
$condition->addCondition("documents.Project_ID='" . Yii::app()->user->projectID . "'");
}
if (Yii::app()->user->userType == UsersClientList::USER
&& is_numeric(Yii::app()->user->projectID)) {
$condition->addCondition("documents.Project_ID='" . Yii::app()->user->projectID . "'");
$condition->addCondition("documents.User_ID='" . Yii::app()->user->userID . "'");
}
if (Yii::app()->user->userType == Users::DATA_ENTRY_CLERK) {
//adding condition to allow DEC see only documents of clients that he has access
$cli_array = Clients::getClientsIDList(Yii::app()->user->userID);
$condition->addInCondition('documents.Client_ID', $cli_array);
}
$condition->order = "documents.Created ASC";
if( $countCond > 0 ) $condition->mergeWith($search_condition);
$payrolls = Payrolls::model()->findAll($condition);
return $payrolls;
}
/**
* Find W9s to data entry module by search query
* If search query is blank returns all W9s to data entry
* @param $queryString
* @param $options
* @return array|CActiveRecord|mixed|null
*/
public function findW9ToEntry($queryString, $options)
{
$w9s = array();
$w9s_to_approve = array();
// get aps to approve
$condition = new CDbCriteria();
$condition_for_temp = new CDbCriteria();
$condition->join = "LEFT JOIN documents ON documents.Document_ID=t.Document_ID
LEFT JOIN clients ON t.Client_ID=clients.Client_ID
LEFT JOIN companies ON clients.Company_ID=companies.Company_ID
LEFT JOIN company_addresses ON company_addresses.Company_ID = companies.Company_ID
LEFT JOIN addresses ON addresses.Address_ID = company_addresses.Address_ID";
$countCond = 0;
$condition_for_temp->addCondition("companies.Temp_Fed_ID_Flag is null");
if (count($options) > 0 && trim($queryString) != '') {
if ($options['search_option_fed_id']) {
$condition->compare('companies.Company_Fed_ID', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_com_name']) {
$condition->compare('companies.Company_Name', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_addr1']) {
$condition->compare('addresses.Address1', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_addr2']) {
$condition->compare('addresses.Address2', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_city']) {
$condition->compare('addresses.City', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_state']) {
$condition->compare('addresses.State', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_zip']) {
$condition->compare('addresses.ZIP', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_country']) {
$condition->compare('addresses.Country', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_phone']) {
$condition->compare('addresses.Phone', $queryString, true, 'OR');
$countCond++;
}
}
if ($countCond == 0 && trim($queryString) != '') {
$condition->compare('companies.Company_Fed_ID', $queryString, true, 'OR');
$condition->compare('companies.Company_Name', $queryString, true, 'OR');
$condition->compare('addresses.Address1', $queryString, true, 'OR');
$condition->compare('addresses.Address2', $queryString, true, 'OR');
$condition->compare('addresses.City', $queryString, true, 'OR');
$condition->compare('addresses.State', $queryString, true, 'OR');
$condition->compare('addresses.ZIP', $queryString, true, 'OR');
$condition->compare('addresses.Country', $queryString, true, 'OR');
$condition->compare('addresses.Phone', $queryString, true, 'OR');
}
if (Yii::app()->user->userType == Users::ADMIN || Yii::app()->user->userType == Users::DATA_ENTRY_CLERK || Yii::app()->user->userType == Users::DB_ADMIN) {
$criteria = clone $condition;
$criteria->addCondition("t.Revision_ID<='0'");
$criteria->addCondition("t.Verified='" . self::NOT_VERIFIED . "'");
$criteria->order = "documents.Created ASC";
$criteria->mergeWith($condition_for_temp);
$w9s_clients = $this->findAll($criteria);
$criteria = clone $condition;
$criteria->addCondition("documents.Client_ID>'0'");
$criteria->addCondition("t.Revision_ID>'0'");
$criteria->addCondition("t.W9_Data_Entry!='" . Yii::app()->user->userID . "'");
$criteria->addCondition("t.Verified='" . self::NOT_VERIFIED . "'");
$criteria->order = "documents.Created ASC";
if (Yii::app()->user->userType == Users::DATA_ENTRY_CLERK) {
//adding condition to allow DEC see only documents of clients that he has access
$cli_array = Clients::getClientsIDList(Yii::app()->user->userID);
$criteria->addInCondition('documents.Client_ID', $cli_array);
}
$criteria->mergeWith($condition_for_temp);
$w9s = $this->findAll($criteria);
foreach($w9s_clients as $key => $value) {
$w9s[] = $value;
}
} else if (Yii::app()->user->userType == Users::USER) {
$criteria = clone $condition;
//commented out 22.01.2015 as there is no sense to connect to users_client_list table
//$criteria->join .= ' LEFT JOIN users_client_list ON documents.Client_ID=users_client_list.Client_ID';
$criteria->addCondition("t.Revision_ID<='0'");
//commented out 22.01.2015 as there is no sense to connect to users_client_list table
//$criteria->addCondition("users_client_list.User_ID='" . Yii::app()->user->userID . "'");
$criteria->addCondition("t.Verified='" . self::NOT_VERIFIED . "'");
//added instead of connection to users_client_list table
$criteria->addCondition("documents.Client_ID='" . Yii::app()->user->clientID . "'");
$criteria->addCondition("documents.Project_ID='" . Yii::app()->user->projectID . "'");
$criteria->addCondition("documents.User_ID='".Yii::app()->user->userID."'");
$criteria->order = "documents.Created ASC";
$criteria->mergeWith($condition_for_temp);
$w9s = $this->findAll($criteria);
} else {
$criteria = clone $condition;
//commented out 22.01.2015 as there is no sense to connect to users_client_list table
//$criteria->join .= ' LEFT JOIN users_client_list ON documents.Client_ID=users_client_list.Client_ID';
$criteria->addCondition("t.Revision_ID<='0'");
//commented out 22.01.2015 as there is no sense to connect to users_client_list table
//$criteria->addCondition("users_client_list.User_ID='" . Yii::app()->user->userID . "'");
$criteria->addCondition("t.Verified='" . self::NOT_VERIFIED . "'");
//added instead of connection to users_client_list table
$criteria->addCondition("documents.Client_ID='" . Yii::app()->user->clientID . "'");
$criteria->addCondition("documents.Project_ID='" . Yii::app()->user->projectID . "'");
$criteria->order = "documents.Created ASC";
$criteria->mergeWith($condition_for_temp);
$w9s = $this->findAll($criteria);
}
return $w9s;
}
/**
* Get POs to enter data
* @return CActiveRecord[]
*/
public static function findPOToEntry()
{
$queryString = $_SESSION['last_po_to_entry_search']['query'];
$options = $_SESSION['last_po_to_entry_search']['options'];
$condition = new CDbCriteria();
$condition->join = "RIGHT JOIN documents ON documents.Document_ID=t.Document_ID";
$condition->addCondition("t.PO_Total IS NULL");
$condition->addCondition("t.Vendor_ID='0'", 'OR');
$condition->addCondition("t.PO_Account_Number IS NULL", 'OR');
$condition->addCondition("t.PO_Approval_Value ='" . Pos::NOT_READY_FOR_APPROVAL . "'", 'OR');
$condition->addCondition("t.Sign_Requested_By='0'");
$condition->addCondition("t.PO_Approval_Value!='100'", 'AND');//this condition for Voided POs - allows hide them from DataEntry
$countCond = 0;
if (count($options) > 0 && trim($queryString) != '') {
$search_condition = new CDbCriteria();
$search_condition->join = 'LEFT JOIN vendors ON t.Vendor_ID = vendors.Vendor_ID ';
$search_condition->join .= 'LEFT JOIN companies ON vendors.Vendor_Client_ID = companies.Company_ID ';
if ($options['search_option_com_name']) {
$search_condition->compare('companies.Company_Name', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_fed_id']) {
$search_condition->compare('companies.Company_Fed_ID', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_account_num']) {
$search_condition->compare('t.PO_Account_Number', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_subtotal']) {
$search_condition->compare('t.PO_Subtotal', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_tax']) {
$search_condition->compare('t.PO_Tax', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_deliv_chg']) {
$search_condition->compare('t.PO_Delivery_Chg', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_other_chg']) {
$search_condition->compare('t.PO_Other_Chg', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_total']) {
$search_condition->compare('t.PO_Total', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_paym_type']) {
$search_condition->compare('t.Payment_Type', $queryString, true, 'OR');
$countCond++;
}
if ($options['search_option_4digits']) {
$search_condition->compare('t.PO_Card_Last_4_Digits', $queryString, true, 'OR');
$countCond++;
}
}
if (Yii::app()->user->userType == UsersClientList::PROCESSOR || Yii::app()->user->userType == UsersClientList::APPROVER
|| Yii::app()->user->userType == UsersClientList::CLIENT_ADMIN) {
$condition->addCondition("documents.Client_ID='" . Yii::app()->user->clientID . "'");
}
if (Yii::app()->user->userType == UsersClientList::PROCESSOR || ((Yii::app()->user->userType == UsersClientList::APPROVER
|| Yii::app()->user->userType == UsersClientList::CLIENT_ADMIN) && is_numeric(Yii::app()->user->projectID))) {
$condition->addCondition("documents.Project_ID='" . Yii::app()->user->projectID . "'");
}
if (Yii::app()->user->userType == Users::USER) {
$condition->addCondition("documents.User_ID='".Yii::app()->user->userID."'");
}
if (Yii::app()->user->userType == Users::DATA_ENTRY_CLERK) {
//adding condition to allow DEC see only documents of clients that he has access
$cli_array = Clients::getClientsIDList(Yii::app()->user->userID);
$condition->addInCondition('documents.Client_ID', $cli_array);
}
$condition->order = "documents.Created ASC";
if( $countCond > 0 ) $condition->mergeWith($search_condition);
$pos = Pos::model()->findAll($condition);
return $pos;
}
