<?php
//the global file operation
include("../public/impOpreation.php");

//global varable
global $showoutside;
include_once("../public/config.php");
include("../public/include_dao.php");
$do = $_GET['do'];

addAllOrMissingRows();

if (empty($do)) {
    $smarty->display("header.html");
    $smarty->display("productstoreview/show.html");
    $smarty->display("footer.html");
} else if ($do == 'allproduct') {
    ###no need addAllOrMissingRows(); will do it
//    try {
//        $products = R::getAll("SELECT product.* FROM `product`  WHERE product.conditions = 0 ");
//        foreach ($products as $product) {
//            $stores = R::getAll("SELECT * FROM `store` WHERE conditions = 0");
//            foreach ($stores as $store) {
//                $productstorecount = R::count("productstore", 'productid = ? and storeid = ? and productstore.conditions = 0', [$product['productId'], $store['storeId']]);
//                if ($productstorecount == 0) {
//                    $productstore = R::dispense('productstore');
//                    $productstore->productid = $product['productId'];
//                    $productstore->storeid = $store['storeId'];
//                    $productstore->productcatid = $product['productCatId'];
//                    $productstore->limitamount = $product['limitamount'];
//                    $productstore->prostorelimitamount = $product['limitamount'];
//                    $productstore->createddate = date("Y-m-d H:i:s");
//                    $productstore->conditions = 0;
//                    R::store($productstore);
//                }
//            }
//        }
//
//        header("location:productstore.php");
//    } catch (Exception $e) {
//        header("location:productstore.php");
//        // header("location:?do=error");
//    }
} else if ($do == 'add') {
    $smarty->display("header.html");
    $smarty->display("productstoreview/add.html");
    $smarty->display("footer.html");
} else if ($do == 'save') {
    try {
        $allproducts = filter_input(INPUT_POST, 'allproducts');
        for ($i = 1; $i <= $allproducts; $i++) {
            $productid = filter_input(INPUT_POST, 'productid' . $i);
            $limitamount = filter_input(INPUT_POST, 'limitamount' . $i);
            $productcatid = filter_input(INPUT_POST, 'productcatid' . $i);
            $allstores = filter_input(INPUT_POST, 'allstores' . $i);
            for ($i2 = 1; $i2 <= $allstores; $i2++) {
                $storeid = filter_input(INPUT_POST, 'storeid' . $i . $i2);
                $prostorelimitamount = (float) filter_input(INPUT_POST, 'prostorelimitamount' . $i . $i2);
                if ($prostorelimitamount < 1) {
                    continue;
                }
                $productstore = R::dispense('productstore');
                $productstore->productid = $productid;
                $productstore->storeid = $storeid;
                $productstore->productcatid = $productcatid;
                $productstore->limitamount = $limitamount;
                $productstore->prostorelimitamount = $prostorelimitamount;
                $productstore->createddate = date("Y-m-d H:i:s");
                $productstore->conditions = 0;
                R::store($productstore);
            }
        }

        header("location:productstore.php");
    } catch (Exception $e) {
        header("location:productstore.php?do=add");
    }
} else if ($do == 'allproducts') {
    $id = filter_input(INPUT_POST, 'id');
    $productcatchild = productcatchild($id);
    $products = R::getAll("SELECT product.* FROM `product`  WHERE product.conditions = 0  and product.productCatId in ($productcatchild) ");
    $allproducts = array();
    foreach ($products as $product) {
        $stores = R::getAll("SELECT * FROM `store` WHERE conditions = 0");
        $productone = array('productid' => $product['productId'], 'productname' => $product['productName'], 'limitamount' => $product['limitamount'], 'productcatid' => $product['productCatId']);
        $productone['stores'] = array();
        foreach ($stores as $store) {
            $productstoreone = R::findOne("productstore", 'productid = ? and storeid = ? and productstore.conditions = 0', [$product['productId'], $store['storeId']]);
            if ($productstoreone) {
                $store['productstoreid'] = $productstoreone->id;
                $store['prostorelimitamount'] = $productstoreone->prostorelimitamount;
            } else {
                $productstore = R::dispense('productstore');
                $productstore->productid = $product['productId'];
                $productstore->storeid = $store['storeId'];
                $productstore->productcatid = $product['productCatId'];
                $productstore->limitamount = $product['limitamount'];
                $productstore->prostorelimitamount = $product['limitamount'];
                $productstore->createddate = date("Y-m-d H:i:s");
                $productstore->conditions = 0;
                $productstoreid = R::store($productstore);
                $store['productstoreid'] = $productstoreid;
                $store['prostorelimitamount'] = $product['limitamount'];
            }
            array_push($productone['stores'], $store);
        }
        if ($productone['stores']) {
            array_push($allproducts, $productone);
        }
    }
    $smarty->assign('allproducts', $allproducts);
    $allstores = R::count("store", ' store.conditions = 0');
    $smarty->assign('allstores', $allstores);
    $smarty->display("productstoreview/allproducts.html");
} else if ($do == 'edit') {
    $id = filter_input(INPUT_GET, 'id');
    $productstore = R::getRow("SELECT productstore.*, storeName, productCatName, productName FROM `productstore`
        LEFT JOIN product ON productstore.productid = product.productId
        LEFT JOIN productcat ON productstore.productcatid = productcat.productCatId
        LEFT JOIN store ON productstore.storeid = store.storeId WHERE productstore.id = ?", [$id]);
    $smarty->assign('productstore', $productstore);
    $smarty->display("header.html");
    $smarty->display("productstoreview/edit.html");
    $smarty->display("footer.html");
} else if ($do == 'update') {
    $id = filter_input(INPUT_POST, 'id');
    $prostorelimitamount = filter_input(INPUT_POST, 'prostorelimitamount');
    try {
        $productstore = R::load('productstore', $id);
        $productstore->prostorelimitamount = $prostorelimitamount;
        R::store($productstore);
        header("location:productstore.php");
    } catch (Exception $e) {
        header("location:productstore.php?do=edit&id=" . $id);
    }
} else if ($do == 'updateAjax') {
    $id = (int) filter_input(INPUT_POST, 'id');
    $prostorelimitamount = (float) filter_input(INPUT_POST, 'val');
    try {
        $productstore = R::load('productstore', $id);
        $productstore->prostorelimitamount = $prostorelimitamount;
        R::store($productstore);
        echo 1;
    } catch (Exception $e) {
        echo -1;
    }
} else if ($do == 'prostorelimitamounts') {
    $id = filter_input(INPUT_POST, 'id');
    $prostorelimitamount = filter_input(INPUT_POST, 'prostorelimitamount');
    R::exec("UPDATE productstore SET prostorelimitamount = $prostorelimitamount WHERE id = $id ");
} else if ($do == 'selectproductcat') {
    selectproductcat();
} else if ($do == 'selectproduct') {
    selectproduct();
} else if ($do == 'selectstore') {
    selectstore();
} else if ($do == 'showajax') {
    showajax();
} else if ($do == 'removeproductstore') {
    removeproductstore();
} elseif ($do == "productBelowLimitInStoreAjax") {
    //edited copy of showajax() in php,js,html
    productBelowLimitInStoreAjax();
}

function selectproductcat() {
    $row_array = array();
    $return_arr = array();
    $searchTerm = $_POST['searchTerm'];
    $productsData = R::getAll("SELECT productCatId, productCatName
    FROM  productcat
    WHERE productcat.conditions = 0 and productCatName LIKE '%" . $searchTerm . "%' limit 50");
    foreach ($productsData as $pro) {
        $row_array['id'] = $pro['productCatId'];
        $row_array['text'] = $pro['productCatName'];
        array_push($return_arr, $row_array);
    }
    echo json_encode($return_arr);
}

function selectproduct() {
    $row_array = array();
    $return_arr = array();
    $searchTerm = $_POST['searchTerm'];
    $productcatid = $_POST['productcat'];
    $productcatchild = productcatchild($productcatid);
    $searchs = '';
    if ($productcatchild) {
        $searchs .= " and product.productCatId in ($productcatchild)";
    }

    $productsData = R::getAll("SELECT productId, productName
    FROM product
    WHERE product.conditions = 0 and productName LIKE '%" . $searchTerm . "%'  $searchs limit 50");
    foreach ($productsData as $pro) {
        $row_array['id'] = $pro['productId'];
        $row_array['text'] = $pro['productName'];
        array_push($return_arr, $row_array);
    }

    echo json_encode($return_arr);
}

function productcatchild($productcatid) {
    $allproductcatid = $productcatid;
    $productscatData = R::getAll("SELECT productCatId FROM  productcat WHERE productcat.conditions = 0 and productCatParent = $productcatid");
    foreach ($productscatData as $cat) {
        $allproductcatid .= ',' . productcatchild($cat['productCatId']);
    }
    return $allproductcatid;
}

function selectstore() {
    $row_array = array();
    $return_arr = array();
    $searchTerm = $_POST['searchTerm'];
    $productsData = R::getAll("SELECT storeId, storeName
    FROM store
    WHERE  store.conditions = 0 and storeName LIKE '%" . $searchTerm . "%'  limit 50");
    foreach ($productsData as $pro) {
        $row_array['id'] = $pro['storeId'];
        $row_array['text'] = $pro['storeName'];
        array_push($return_arr, $row_array);
    }

    echo json_encode($return_arr);
}

function showajax() {
    $columns = array('productstore.id', 'productName', 'productCatName', 'storeName', 'productstore.limitamount', 'prostorelimitamount', 'createddate', 'productstore.id', 'productstore.conditions');
    $start_date = filter_input(INPUT_POST, 'start_date');
    $end_date = filter_input(INPUT_POST, 'end_date');
    $data1 = filter_input(INPUT_POST, 'data1');
    $data2 = filter_input(INPUT_POST, 'data2');
    $data3 = filter_input(INPUT_POST, 'data3');


    $searchQuery = " ";
    if ($data1 != '') {
        $searchQuery .= " and productstore.productcatid = " . $data1 . " ";
    }

    if ($data2 != '') {
        $searchQuery .= " and productstore.productid = " . $data2 . " ";
    }
    if ($data3 != '') {
        $searchQuery .= " and productstore.storeid = " . $data3 . " ";
    }


    if ($start_date != '' && $end_date != '') {
        $searchQuery .= ' and  productstore.createddate  >= "' . $start_date . ' 00-00-00" and productstore.createddate <= "' . $end_date . ' 23-59-55" ';
    }




    if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
        $searchQuery .= " and ( ";
        for ($i = 1; $i < count($columns); $i++) {
            $searchQuery .= $columns[$i] . " LIKE '%" . trim($_POST['search']['value']) . "%' OR ";
        }
        $searchQuery = substr_replace($searchQuery, "", -3);
        $searchQuery .= ") ";
        $searchQuery .= " productstore.id LIKE " % '.$_POST["search"]["value"].' % " )";
    }


    if (isset($_POST["order"])) {
        $searchQuery .= 'ORDER BY ' . $columns[$_POST['order']['0']['column']] . ' ' . $_POST['order']['0']['dir'] . '  ';
    } else {
        $searchQuery .= "ORDER BY  productstore.id DESC ";
    }

    $totals = R::count('productstore', 'LEFT JOIN product ON productstore.productid = product.productId
        LEFT JOIN productcat ON productstore.productcatid = productcat.productCatId
        LEFT JOIN store ON productstore.storeid = store.storeId
         WHERE productstore.conditions = 0  ' . $searchQuery . ' ');

    if (isset($_POST['start']) && $_POST['length'] != '-1') {
        $searchQuery .= "LIMIT " . intval($_POST['start']) . ", " .
                intval($_POST['length']);
    }

    $rResult = R::getAll("SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $columns)) . "
        FROM productstore
        LEFT JOIN product ON productstore.productid = product.productId
        LEFT JOIN productcat ON productstore.productcatid = productcat.productCatId
        LEFT JOIN store ON productstore.storeid = store.storeId
        WHERE productstore.conditions = 0  $searchQuery");
    $i = 0;
    $output = array(
        "draw" => intval(filter_input(INPUT_POST, 'draw')),
        "recordsTotal" => $i,
        "recordsFiltered" => intval($totals),
        "data" => array()
    );

    foreach ($rResult as $row) {
        $sub_array = array();
        $sub_array[] = $row["id"];
        $sub_array[] = $row["productName"];
        $sub_array[] = $row["productCatName"];
        $sub_array[] = $row["storeName"];
        $sub_array[] = $row["limitamount"];
        $sub_array[] = '<input type="text" class="form-control saveThis positivefloat" name="prostorelimitamount' . $i . '" id="prostorelimitamount' . $i . '" data-id="' . $row["id"] . '" value="' . $row["prostorelimitamount"] . '" tabindex="10">';
        $sub_array[] = $row["createddate"];

        if ($row["conditions"] == 0) {
            $sub_array[] = '<a href="productstore.php?do=edit&id=' . $row["id"] . '" type="button" class="btn btn-default btn-lg editicon"></a>';
            $sub_array[] = '<a href="javascript:;" data-id="' . $row["id"] . '"  type="button" class="btn btn-default btn-lg removeicon  removeproductstore"  ></a>';
        } else {
            $sub_array[] = 'محذوف ';
            $sub_array[] = 'محذوف ';
        }
        $i++;

        $output['data'][] = $sub_array;
    }

    // $sub_array2 = array();
    // $sub_array2[] = "";
    // $sub_array2[] = "";
    // $sub_array2[] = "";
    // $sub_array2[] = (int)$buybillfinalbilltotal;
    // $sub_array2[] = "";
    // $sub_array2[] = date("Y-m-d H:i:s");
    // $sub_array2[] = (int) $sellbillfinalbilltotal;
    // $sub_array2[] = "";
    // $sub_array2[] = (int) $finaltotals;
    // $sub_array2[] = "";
    // $sub_array2[] = "";
    // $output['data'][] = $sub_array2;

    echo json_encode($output, JSON_UNESCAPED_SLASHES);
}

function productBelowLimitInStoreAjax() {
    $columns = array('productstore.id', 'productName', 'productCatName', 'storeName', 'productstore.limitamount', 'prostorelimitamount', 'productquantity');

    $userStore = (int) $_SESSION['userstore'];
    $searchQuery = " and productstore.storeid = $userStore and storedetail.productquantity < productstore.prostorelimitamount ";
    if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
        $searchQuery .= " and ( ";
        for ($i = 1; $i < count($columns); $i++) {
            $searchQuery .= $columns[$i] . " LIKE '%" . trim($_POST['search']['value']) . "%' OR ";
        }
        $searchQuery = substr_replace($searchQuery, "", -3);
        $searchQuery .= ") ";
        $searchQuery .= " productstore.id LIKE " % '.$_POST["search"]["value"].' % " )";
    }


    if (isset($_POST["order"])) {
        $searchQuery .= 'ORDER BY ' . $columns[$_POST['order']['0']['column']] . ' ' . $_POST['order']['0']['dir'] . '  ';
    } else {
        $searchQuery .= "ORDER BY  productstore.id DESC ";
    }

    $totals = R::count('productstore', 'JOIN product ON productstore.productid = product.productId
        JOIN productcat ON productstore.productcatid = productcat.productCatId
        JOIN storedetail ON (storedetail.storeid = productstore.storeid and storedetail.productid=productstore.productid)
        JOIN store ON productstore.storeid = store.storeId
        WHERE productstore.conditions = 0 and product.conditions=0 and product.isService=0 ' . $searchQuery . ' ');

    if (isset($_POST['start']) && $_POST['length'] != '-1') {
        $searchQuery .= "LIMIT " . intval($_POST['start']) . ", " .
                intval($_POST['length']);
    }

    $rResult = R::getAll("SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $columns)) . "
        FROM productstore
        JOIN product ON productstore.productid = product.productId
        JOIN productcat ON productstore.productcatid = productcat.productCatId
        JOIN storedetail ON (storedetail.storeid = productstore.storeid and storedetail.productid=productstore.productid)
        JOIN store ON productstore.storeid = store.storeId
        WHERE productstore.conditions = 0 and product.conditions=0 and product.isService=0 $searchQuery");
    $i = 1;
    $output = array(
        "draw" => intval(filter_input(INPUT_POST, 'draw')),
        "recordsTotal" => $i,
        "recordsFiltered" => intval($totals),
        "data" => array()
    );

    foreach ($rResult as $row) {
        $sub_array = array();
        $sub_array[] = $i; //$row["id"];
        $sub_array[] = $row["productName"];
        $sub_array[] = $row["productCatName"];
        $sub_array[] = $row["storeName"];
        //$sub_array[] = $row["limitamount"];
        $sub_array[] = $row["prostorelimitamount"];
        $sub_array[] = (float) $row["productquantity"];

        $i++;
        $output['data'][] = $sub_array;
    }

    echo json_encode($output, JSON_UNESCAPED_SLASHES);
}

function removeproductstore() {
    $id = filter_input(INPUT_POST, 'id');
    $productstore = R::load('productstore', $id);
    $productstore->conditions = 1;
    try {
        R::store($productstore);
        echo 1;
    } catch (Exception $e) {
        echo 0;
    }
}

function addAllOrMissingRows() {
    $dbName = R::getCell("SELECT DATABASE()");
    $rowsNo = R::getCell("SELECT table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" . $dbName . "' and TABLE_NAME = 'productstore'");
    if ($rowsNo > 0) {
        //1- make sure unique key exist drop ,recreate it
        try {
            $res = R::exec("ALTER TABLE productstore DROP INDEX `productstoreunique`");
        } catch (Exception $exc) {

        }
    }
    try {
        $res = R::exec("ALTER TABLE productstore ADD UNIQUE `productstoreunique` ( `productid`, `storeid`)");
    } catch (Exception $exc) {

    }
    //2- insert what is missing
    $res = R::exec("INSERT IGNORE INTO productstore (productid, storeid, productcatid,limitamount,prostorelimitamount,createddate,conditions)
                        SELECT product.productId, storedetail.storeid, product.productCatId, product.limitamount, product.limitamount,'" . date('Y-m-d H:i:s') . "',0
                        from storedetail
                        join product on (product.productId = storedetail.productid and product.conditions=0)");
}
?>