File: /home/mostafedeg/public_html/erp/models/mysql/ext/ClientMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'client'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2013-04-06 10:57
*/
class ClientMySqlExtDAO extends ClientMySqlDAO {
public function countAddressOccurance($value) {
$sql = 'SELECT count(*) FROM client WHERE clientaddress = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($value);
return $this->querySingleResult($sqlQuery);
}
public function insertFirstClient($client) {
$sql = 'INSERT INTO client (clientid, clientname, clientaddress, clientphone, clientmobile, clientdebt, clientdetails, conditions, clientdate, userid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($client->clientid);
$sqlQuery->set($client->clientname);
$sqlQuery->set($client->clientaddress);
$sqlQuery->set($client->clientphone);
$sqlQuery->set($client->clientmobile);
$sqlQuery->set($client->clientdebt);
$sqlQuery->setString2($client->clientdetails);
$sqlQuery->setNumber($client->conditions);
$sqlQuery->set($client->clientdate);
$sqlQuery->setNumber($client->userid);
$this->executeInsert($sqlQuery);
}
public function getClientByRFID($rfid) {
$sql = 'SELECT *
FROM client
WHERE clientRFID = "' . $rfid . '" and conditions = 0';
//print_r($sql . '<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function querybyarea($clientareaid, $clientDate) {
$sql = 'SELECT client.* , clientarea.name as clientareaName
FROM client
join clientarea on clientarea.id = client.clientareaid
WHERE ' . $clientareaid . '
' . $clientDate . '
order by clientid desc';
//print_r($sql . '<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function querybyarea2($clientareaid) {
$sql = 'SELECT client.* , clientarea.name as clientareaName
FROM client
join clientarea on clientarea.id = client.clientareaid
WHERE client.clientareaid = ' . $clientareaid . '
order by clientid desc';
//print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updatedept($client) {
$sql = 'UPDATE client SET clientdebt = ? , clientdate = ?, userid = ? WHERE clientid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($client->clientdebt);
$sqlQuery->set($client->clientdate);
$sqlQuery->setNumber($client->userid);
$sqlQuery->setNumber($client->clientid);
return $this->executeUpdate($sqlQuery);
}
public function updatedeptlimit($debtLimit) {
$sql = 'UPDATE client SET debtLimit = ' . $debtLimit;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function updatedept1($clientid, $clientdebt) {
$sql = 'UPDATE client SET clientdebt = "' . $clientdebt . '"
WHERE clientid = "' . $clientid . '"';
$sqlQuery = new SqlQuery($sql);
//print_r($sqlQuery);
return $this->executeUpdate($sqlQuery);
}
public function updateClientDelegateid($clientid, $delegateid) {
$sql = 'UPDATE client SET delegateid = ' . $delegateid . ' where clientid = ' . $clientid;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function loadExt($id) {
$sql = 'SELECT * FROM client WHERE clientid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getList($sqlQuery);
}
//pagination
public function queryAllWithLimit($startpoint, $perpage) {
$sql = 'SELECT * FROM client
ORDER BY clientid ASC
limit ' . $startpoint . ',' . $perpage . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllForShow() {
$sql = 'SELECT client.*,user.employeename,lasteditone.employeename as lastEditUserName,deligate.employeename as deligateName
FROM client
left join user on user.userid = client.userid
left join user as lasteditone on lasteditone.userid = client.lastEditUser
left join user as deligate on deligate.userid = client.delegateid
WHERE client.conditions = 0
ORDER BY clientid ASC ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllsup() {
$sql = 'SELECT * FROM client WHERE conditions=0 ORDER BY clientdate DESC ,clientid DESC ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
/*
* Update record in table to make conditions field = 1 to mark deleted status.
*/
public function deletetemp($clientid) {
$sql = 'UPDATE client SET conditions = 1
WHERE clientid =' . $clientid . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function returndelete($clientid) {
$sql = 'UPDATE client SET conditions = 0
WHERE clientid =' . $clientid . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function queryByClientNameExt($input) {
$sql = 'SELECT client.*,user.employeename,lasteditone.employeename as lastEditUserName
FROM client
left join user on user.userid = client.userid
left join user as lasteditone on lasteditone.userid = client.lastEditUser
WHERE clientname LIKE "' . $input . '%"
ORDER BY clientdate DESC ,clientid DESC ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByClientNameExtLimited($input, $start, $end) {
$sql = 'SELECT *
FROM client
WHERE clientname LIKE "' . $input . '%"
ORDER BY clientdate DESC ,clientid DESC
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallExtLimited($input) {
$sql = 'SELECT clientname,clientid,clientmobile,clientdebt
FROM client
WHERE (clientname LIKE "%' . $input . '%"
or clientphone LIKE "' . $input . '%"
or clientmobile LIKE "' . $input . '%"
or clientcode LIKE "' . $input . '%") and conditions = 0
ORDER BY clientdate DESC ,clientid DESC
';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updateClientdebt($client) {
$sql = 'UPDATE client SET clientdebt = ?, userid = ? WHERE clientid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($client->clientdebt);
$sqlQuery->setNumber($client->userid);
$sqlQuery->setNumber($client->clientid);
return $this->executeUpdate($sqlQuery);
}
public function getClientsTotalDebt() {
$sql = 'SELECT SUM(clientdebt)
FROM client
WHERE conditions=0';
$sqlQuery = new SqlQuery($sql);
return $this->QuerySingleResult($sqlQuery);
}
public function queryAllClientsEX() {
$sql = 'SELECT client.clientid,client.clientname
FROM client WHERE conditions=0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryClientsEX($branchId) {
$sql = 'SELECT client.clientid,client.clientname
FROM client WHERE conditions=0 AND branchId = ' . $branchId . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getClients1($name) {
$sql = 'SELECT client.clientid AS value ,client.clientname AS label
FROM client
WHERE clientname LIKE "%' . $name . '%"
AND conditions=0
AND clientname != ""';
$sqlQuery = new SqlQuery($sql);
//print_r($sqlQuery);
return $this->getList($sqlQuery);
}
public function getClients2($name) {
$sql = 'SELECT client.clientid AS value ,client.clientphone AS label
FROM client
WHERE clientphone LIKE "%' . $name . '%"
AND conditions=0
AND clientphone != ""';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllbyarea($clientareaid) {
$sql = 'SELECT * FROM client where clientareaid = ' . $clientareaid . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getClientsTotalDebt2() {
$sql = 'SELECT SUM(clientdebt)
FROM client
WHERE conditions=0
';
$sqlQuery = new SqlQuery($sql);
return $this->QuerySingleResult($sqlQuery);
}
public function getClientsforoffer($name) {
$sql = 'SELECT client.clientid AS value ,client.clientname AS label
FROM client
WHERE clientname LIKE "%' . $name . '%"
AND conditions=0
AND clientname != ""';
// print_r("<br>".$sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function insertclient($client) {
$sql = 'INSERT INTO client (clientname,clientdebt,clientdetails) VALUES (?,?,?)';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($client->clientname);
$sqlQuery->set($client->clientdebt);
$sqlQuery->set($client->clientdetails);
$id = $this->executeInsert($sqlQuery);
$client->clientid = $id;
return $id;
}
public function insertclientstudents($client) {
$sql = 'INSERT INTO client (clientname,clientphone,clientaddress,branchId,clientdate,userid)
VALUES (?,?,?,?,?,?)';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($client->clientname);
$sqlQuery->set($client->clientphone);
$sqlQuery->set($client->clientaddress);
$sqlQuery->set($client->branchid);
$sqlQuery->set($client->clientdate);
$sqlQuery->set($client->userid);
$id = $this->executeInsert($sqlQuery);
$client->clientid = $id;
return $id;
}
public function updateclientstudents($client) {
$sql = 'UPDATE client SET clientname = ?, clientphone = ?, clientaddress = ?, branchId = ? WHERE clientid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($client->clientname);
$sqlQuery->set($client->clientphone);
$sqlQuery->set($client->clientaddress);
$sqlQuery->set($client->branchid);
$sqlQuery->setNumber($client->clientid);
return $this->executeUpdate($sqlQuery);
}
public function updateClientname($client) {
$sql = 'UPDATE client SET clientname = ?, clientdebt = ?, clientdetails = ? WHERE clientid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($client->clientname);
$sqlQuery->set($client->clientdebt);
$sqlQuery->set($client->clientdetails);
$sqlQuery->setNumber($client->clientid);
return $this->executeUpdate($sqlQuery);
}
public function queryByRondomtxt($value) {
$sql = 'SELECT * FROM client WHERE rondomtxt = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($value);
return $this->getRow($sqlQuery);
}
public function clientsWithoutBills($queryString1, $queryString, $queryStringR, $queryString1R, $queryString1SR) {
$sql = 'SELECT * from client where client.conditions = 0 and client.clientid
NOT IN
(
SELECT sellbill.sellbillclientid FROM `sellbill`
join client on client.clientid = sellbill.sellbillclientid
' . $queryString1 . '
)
and client.clientid
NOT IN
(
SELECT client.clientid FROM `bills`
join client on client.clientid = bills.clientid
' . $queryString . '
)
and client.clientid
NOT IN
(
SELECT client.clientid FROM `billsreturn`
join bills on bills.id = billsreturn.billid
join client on client.clientid = bills.clientid
' . $queryStringR . '
)
and client.clientid
NOT IN
(
SELECT client.clientid FROM `returnsellbill`
join client on client.clientid = returnsellbill.returnsellbillclientid
' . $queryString1R . '
)
and client.clientid
NOT IN
(
SELECT client.clientid FROM `sellbillandrutern`
join client on client.clientid = sellbillandrutern.sellbillclientid
' . $queryString1SR . '
)
';
// echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getClientsAndGovernmentAndArea($queryString) {
$sql = 'SELECT client.clientid as clientid , client.clientdebt as clientdebt , client.clientareaid as clientareaid ,client.typeclientid as typeclientid, government.governmetid as governmetid,
client.clientname as clientname , clientarea.name as clientareaName , government.governmentname as governmentname
FROM `client`
join goverarea on goverarea.clientareaid = client.clientareaid
join clientarea on clientarea.id = client.clientareaid
join government on government.governmetid = goverarea.governmentid
' . $queryString . ' order by client.clientid';
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//azmy
public function getClientsAndGovernmentAndAreas($queryString, $queryStringTy) {
$sql = 'SELECT client.clientid as clientid , client.clientdebt as clientdebt , client.clientareaid as clientareaid ,client.typeclientid as typeclientid, government.governmetid as governmetid,
client.clientname as clientname , clientarea.name as clientareaName , government.governmentname as governmentname
FROM `client`
join goverarea on goverarea.clientareaid = client.clientareaid
join clientarea on clientarea.id = client.clientareaid
join government on government.governmetid = goverarea.governmentid
' . $queryString . ' ' . $queryStringTy . ' order by client.clientid ';
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getClientsAndGovernmentAndAreafromdeptchange($clientID) {
$sql = 'SELECT * from clientdebtchange where clientid = ' . $clientID;
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByAreaidSimple($value) {
$sql = 'SELECT clientid FROM client WHERE clientareaid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryByGovididSimple($value) {
$sql = 'SELECT client.clientid
FROM client
join goverarea on goverarea.clientareaid = client.clientareaid
WHERE goverarea.governmentid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function getAutoIncrementValue($DBName) {
$sql = 'SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = "' . $DBName . '"
AND TABLE_NAME = "client" ';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryByTypeclientid($typeclientid) {
$sql = 'SELECT client.*,user.employeename,lasteditone.employeename as lastEditUserName
FROM client
left join user on user.userid = client.userid
left join user as lasteditone on lasteditone.userid = client.lastEditUser
WHERE client.conditions = 0 and (typeclientid like "%,' . $typeclientid . ',%" or typeclientid like "%,-20,%") ';
// old was "%' . $typeclientid . '%" with out the two commas ,
//or like "%,-20,%" to show client with type -20 in all other types tab
$sqlQuery = new SqlQuery($sql);
// $sqlQuery->set($typeclientid);
return $this->getList($sqlQuery);
}
public function queryByTypeclientidEqual($typeclientid) {
$sql = 'SELECT client.*,user.employeename,lasteditone.employeename as lastEditUserName
FROM client
left join user on user.userid = client.userid
left join user as lasteditone on lasteditone.userid = client.lastEditUser
WHERE client.conditions = 0 and typeclientid = "' . $typeclientid . '" ';
$sqlQuery = new SqlQuery($sql);
// $sqlQuery->set($typeclientid);
return $this->getList($sqlQuery);
}
public function queryByTypeclientconditions() {
$sql = 'SELECT client.*,user.employeename,lasteditone.employeename as lastEditUserName
FROM client
left join user on user.userid = client.userid
left join user as lasteditone on lasteditone.userid = client.lastEditUser
WHERE client.conditions = 1 ';
$sqlQuery = new SqlQuery($sql);
// $sqlQuery->set($typeclientid);
return $this->getList($sqlQuery);
}
public function getClientsAndAreaSelect2($name) {
$sql = 'SELECT client.clientid,CONCAT(client.clientname, " - ", clientarea.name) as clientname
FROM client
join clientarea on clientarea.id = client.clientareaid
WHERE client.conditions = 0
and (client.clientname like "%' . $name . '%" or clientarea.name like "%' . $name . '%")';
//print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function runSelectQuery($sql) {
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//it gets the count of products that is not optic
public function getClientsCountForCURLNormal($sqlquery = '') {
$sql = 'SELECT count(clientid) as clientid
FROM client
where conditions=0 ' . $sqlquery;
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryAllOrderedLimitedSimpleForCurlNormal($start, $end, $sqlquery = '') {
$sql = 'SELECT *
FROM client
where conditions=0 ' . $sqlquery . ' and clientid > ' . $start . '
order by clientid asc
limit ' . $end . '';
//var_dump($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllOrderedLimitedSimpleForCurlNormal2($limit, $sqlquery = '') {
$sql = 'SELECT *
FROM client
where conditions=0 ' . $sqlquery . '
order by clientid asc
limit ' . $limit . '';
//var_dump($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getalldata($clientID) {
$sql = 'SELECT client.*,clientarea.name as areaName,typeclient.typeName FROM client
left join clientarea on clientarea.id = client.clientareaid
left join typeclient on typeclient.typeId = client.typeclientid
WHERE client.conditions = 0
and client.clientid = ' . $clientID;
// print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function loadGetalldata2($clientid) {
$sql = 'SELECT client.*, government.governmetid as governmetid,client.clientname as clientname,clientarea.name as clientareaName,government.governmentname as governmentname
FROM `client`
left join goverarea on goverarea.clientareaid = client.clientareaid
left join clientarea on clientarea.id = client.clientareaid
left join government on government.governmetid = goverarea.governmentid
WHERE client.clientid = ' . $clientid;
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function getClientsDebtAfterRevision() {
$sql = 'SELECT client.clientid,client.clientname,client.clientdebt,t.clientdebtcalc
FROM client
join clienttemp t on t.clientid = client.clientid
WHERE client.clientdebt!=t.clientdebtcalc';
//print_r($sql . '<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function markClientAsNOTInUse($clientid = 0) {
$sql = 'UPDATE client SET inUse = 0 where clientid = ' . $clientid;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function createClientInUseSP() {
$_SESSION['stop_checking_result'] = 1;
$sql = 'DROP PROCEDURE IF EXISTS clientInUse;';
$sqlQuery = new SqlQuery($sql);
$this->executeUpdate($sqlQuery);
//create procedure goes row by row to re calc debtbefore and after for each row in clientdebtchangetemp
$sql = '
CREATE PROCEDURE clientInUse(IN id INT,OUT debtis VARCHAR(256),OUT nameis VARCHAR(256),OUT treeIdis VARCHAR(256))
BEGIN
DECLARE in_use_flag INT DEFAULT 1;
SELECT inUse FROM client WHERE clientid = id INTO in_use_flag;
IF in_use_flag = 0 THEN
update client set inUse=1 where clientid = id;
SELECT clientdebt,clientname,treeId INTO debtis,nameis,treeIdis FROM client WHERE clientid = id ;
ELSE
SELECT "in_use","in_use","in_use" INTO debtis,nameis,treeIdis;
END IF;
END
';
$sqlQuery = new SqlQuery($sql);
$affected_rows = $this->executeUpdate($sqlQuery);
unset($_SESSION['stop_checking_result']);
return $affected_rows;
}
public function callClientInUseSP($clientId) {
if ($clientId > 1) {
$mysqli = new mysqli(ConnectionProperty::getHost(), ConnectionProperty::getUser(), ConnectionProperty::getPassword(), ConnectionProperty::getDatabase());
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
##checks
$callSql = "CALL clientInUse($clientId,@clientdebt,@clientname,@treeIdis)";
$myfile = fopen("backup/" . date("Y-m-d") . ".txt", "a+") or die("Unable to open file!");
$txt = $callSql . ";\r\n";
fwrite($myfile, $txt);
fclose($myfile);
if (!$mysqli->query("SET @clientdebt='in_use',@clientname='in_use',@treeIdis='in_use'") || !$mysqli->query($callSql)) {
echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($res = $mysqli->query("SELECT @clientdebt as clientdebt,@clientname as clientname,@treeIdis as treeId"))) {
echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$row = $res->fetch_assoc();
$rowAsObj = new stdClass();
foreach ($row as $key => $value) {
$rowAsObj->$key = $value;
}
return $rowAsObj;
}
}
// public function clientdeptbytypeclient($queryStringTy) {
// $sql = 'SELECT client.*,clientarea.name as areaName,government.governmentname , FROM client
// LEFT JOIN clientarea on client.clientareaid = clientarea.id
// LEFT JOIN goverarea on client.clientareaid = goverarea.clientareaid
// LEFT JOIN government on goverarea.governmentid = government.governmetid where
// ' . $queryStringTy.' ORDER BY clientid DESC';
//// echo $sql;
// $sqlQuery = new SqlQuery($sql);
// return $this->getList($sqlQuery);
// }
public function getCountByColumnEqualNotDel($column, $value, $queryString = '') {
$sql = 'SELECT count(*) FROM client WHERE conditions = 0 and ' . $column . '= "' . $value . '" ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryAllForShowaz() {
$sql = 'SELECT client.*,user.employeename,lasteditone.employeename as lastEditUserName
FROM client
left join user on user.userid = client.userid
left join user as lasteditone on lasteditone.userid = client.lastEditUser WHERE client.conditions = 1
ORDER BY clientid ASC ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllTamweenDa3mStatus($queryStringJoin = '', $queryString = '') {
$sql = 'SELECT client.clientid,client.clientcode,client.clientname,da3mStatus,tamweenda3mstatus.id as tamweenda3mstatusid,user.employeename,tamweenda3mstatus.sysdate,cardNum,noOfPersonsDa3m,cardPassword
FROM client
join tamweenclientdetail on tamweenclientdetail.clientid = client.clientid
left join tamweenda3mstatus on (client.clientid = tamweenda3mstatus.clientid ' . $queryStringJoin . ')
left join user on user.userid = tamweenda3mstatus.userid
where 1 ' . $queryString;
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllTamweenDa3mStatus2($queryStringJoin = '', $queryString = '') {
$sql = 'SELECT client.clientid,client.clientcode,client.clientname,tamweenStatus,tamweenda3mstatus.id as tamweenda3mstatusid,user.employeename,tamweenda3mstatus.sysdateTamween as sysdate,cardNum,noOfPersonsTamween,cardPassword
FROM client
join tamweenclientdetail on tamweenclientdetail.clientid = client.clientid
left join tamweenda3mstatus on (client.clientid = tamweenda3mstatus.clientid ' . $queryStringJoin . ')
left join user on user.userid = tamweenda3mstatus.useridTamween
where 1 ' . $queryString;
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function sumDebtByQueryString($queryString) {
$sql = 'SELECT sum(clientdebt) as clientdebt
FROM client ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function updateButNotDept($client) {
$sql = 'UPDATE client SET clientname = ?, clientaddress = ?, clientphone = ?, clientmobile = ?, clientdetails = ?, conditions = ?, clientdate = ?, userid = ? , clientareaid = ? ,clientcode = ?,dailyentryid = ? , rondomtxt = ?,clientStoreIds = ?, obygyPatientId = ? , debtLimit=? , typeclientid=?,priceTypeId=?,lastEditUser = ?,inUse = ?,card_number = ?,file_faida = ?,specialDiscount = ?,specialDiscountVal = ?,file = ?,mandobCollectRatio=?,clientRFID = ?,postponeDays=?,taxnumber=?, password=?,clientTypeForTree=?,treeId=?,clientaddress2=?,country=?,delegateid=? WHERE clientid = ?'; //, clientdebt = ?
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($client->clientname);
$sqlQuery->set($client->clientaddress);
$sqlQuery->set($client->clientphone);
$sqlQuery->set($client->clientmobile);
//$sqlQuery->set($client->clientdebt);
$sqlQuery->setString2($client->clientdetails);
$sqlQuery->setNumber($client->conditions);
$sqlQuery->set($client->clientdate);
$sqlQuery->setNumber($client->userid);
$sqlQuery->setNumber($client->clientareaid);
$sqlQuery->set($client->clientcode);
$sqlQuery->setNumber($client->dailyentryid);
$sqlQuery->set($client->rondomtxt);
$sqlQuery->set($client->clientStoreIds);
$sqlQuery->set((int) $client->obygyPatientId);
$sqlQuery->setNumber((float) $client->debtLimit);
$sqlQuery->set($client->typeclientid);
$sqlQuery->setNumber((int) $client->priceTypeId);
$sqlQuery->setNumber((int) $client->lastEditUser);
$sqlQuery->setNumber((int) $client->inUse);
$sqlQuery->setNumber((int) $client->card_number);
$sqlQuery->set($client->file_faida);
$sqlQuery->setNumber((int) $client->specialDiscount);
$sqlQuery->setNumber((float) $client->specialDiscountVal);
$sqlQuery->set($client->file);
$sqlQuery->setNumber((float) $client->mandobCollectRatio);
$sqlQuery->set($client->clientRFID);
$sqlQuery->set((int) $client->postponeDays);
$sqlQuery->set($client->taxnumber);
$sqlQuery->set($client->password);
$sqlQuery->setNumber((int) $client->clientTypeForTree);
$sqlQuery->setNumber((int) $client->treeId);
$sqlQuery->set($client->clientaddress2);
$sqlQuery->set($client->country);
$sqlQuery->setNumber((int) $client->delegateid);
$sqlQuery->setNumber($client->clientid);
return $this->executeUpdate($sqlQuery);
}
public function removeAnyClientLinkForASupplier($linkedSupplierId) {
$sql = 'UPDATE client SET linkedSupplierId = 0 WHERE linkedSupplierId = ' . $linkedSupplierId;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function queryAllWithQueryString($queryString) {
$sql = 'SELECT SQL_CACHE *
FROM client where 1 ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getclientPremium() {
$sql = 'SELECT premiumclient.client_id ,client.clientname,client.clientid
FROM premiumclient join client on premiumclient.client_id = client.clientid group BY premiumclient.client_id';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByCondition($queryString) {
$sql = 'SELECT SQL_CACHE *
FROM client where 1 ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByHusbandNameObgy($value) {
$sql = 'SELECT * FROM client WHERE husbandNameObgy = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($value);
return $this->getList($sqlQuery);
}
public function freeClient($clientid) {
$sql = 'UPDATE client SET inUse=0 WHERE clientid = ' . $clientid;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function getAllClientsExceptId($clientid) {
$sql = 'SELECT * FROM client where clientid != ' . $clientid;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function clientsWithoutBillsWithArea($areaQS = '', $queryString1, $queryString, $queryStringR, $queryString1R, $queryString1SR) {
$sql = 'SELECT * from client where client.conditions = 0
' . $areaQS . ' and client.clientid
NOT IN
(
SELECT sellbill.sellbillclientid FROM `sellbill`
join client on client.clientid = sellbill.sellbillclientid
' . $queryString1 . '
)
and client.clientid
NOT IN
(
SELECT client.clientid FROM `bills`
join client on client.clientid = bills.clientid
' . $queryString . '
)
and client.clientid
NOT IN
(
SELECT client.clientid FROM `billsreturn`
join bills on bills.id = billsreturn.billid
join client on client.clientid = bills.clientid
' . $queryStringR . '
)
and client.clientid
NOT IN
(
SELECT client.clientid FROM `returnsellbill`
join client on client.clientid = returnsellbill.returnsellbillclientid
' . $queryString1R . '
)
and client.clientid
NOT IN
(
SELECT client.clientid FROM `sellbillandrutern`
join client on client.clientid = sellbillandrutern.sellbillclientid
' . $queryString1SR . '
)
';
// echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
}