<?php
//############################################################################//
/*  Last Modified       : 20/Aug/2025                                        */
/*  Script Name         : list_crm.php                                        */
//============================================================================//

//============================================================================//
// Add customer relationship module between merchant and affiliates           //
// List customer relationship module between merchant and affiliates          //
//============================================================================//
//############################################################################//

session_start();

//=======================================================================
// Retrieve variables from GET safely
$crm_affiliate_id = filter_input(INPUT_GET, 'crm_affiliate_id', FILTER_VALIDATE_INT);
$crm_cat_id       = filter_input(INPUT_GET, 'crm_cat_id', FILTER_VALIDATE_INT);
$crm_date         = filter_input(INPUT_GET, 'crm_date', FILTER_SANITIZE_STRING);
$crm_subject      = filter_input(INPUT_GET, 'crm_subject', FILTER_SANITIZE_STRING);
$crm_note         = filter_input(INPUT_GET, 'crm_note', FILTER_SANITIZE_STRING);
$crm_flag         = filter_input(INPUT_GET, 'crm_flag', FILTER_SANITIZE_STRING);
$crm_action       = filter_input(INPUT_GET, 'crm_action', FILTER_SANITIZE_STRING);

// List filter options
$list_by          = filter_input(INPUT_GET, 'list_By', FILTER_SANITIZE_STRING);

// Order options
$order_seq        = filter_input(INPUT_GET, 'order_seq', FILTER_SANITIZE_STRING);
$order_by         = filter_input(INPUT_GET, 'order_By', FILTER_SANITIZE_STRING);

// Error message code
$errCode          = filter_input(INPUT_GET, 'errMsg', FILTER_SANITIZE_STRING);

// Merchant ID from session
$crm_merchant_id  = isset($_SESSION['CRMMERCHANT']) ? intval($_SESSION['CRMMERCHANT']) : 0;

// Default button caption
$butCaption       = "Add New";

// Mode and CRM ID
$mode             = filter_input(INPUT_GET, 'mode', FILTER_SANITIZE_STRING);
$crm_id           = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_STRING);

// Toggle ASC/DESC and set image indicator
if ($order_seq === "DESC") {
    $orderByValue = "ASC";
    $image        = "../images/up.gif";
} else {
    $orderByValue = "DESC";
    $image        = "../images/dawn.gif";
}

// Default/neutral image
$imageNormal = "../images/normal.gif";

// End Here

#=======================================================================



#=======================================================================
# checking for edit mode

 if ($crm_id) {
    if ($mode === "Edit") {
        $butCaption = "Edit";

        $stmt = $conn->prepare("
            SELECT *,
                DATE_FORMAT(crm_date, '%d/%m/%Y') AS CRM_DATE,
                DATE_FORMAT(crm_crdate, '%d/%m/%Y') AS CRM_CRDATE
            FROM partners_crm
            WHERE crm_id = ?
        ");
        $stmt->bind_param("s", $crm_id);
        $stmt->execute();
        $result = $stmt->get_result();

        if ($result && $result->num_rows > 0) {
            $rowEditCrm = $result->fetch_object();
            $crm_affiliate_id = $rowEditCrm->crm_affiliateid;
            $crm_cat_id       = $rowEditCrm->crm_cat_id;
            $crm_id           = $rowEditCrm->crm_id;
            $crm_date         = $rowEditCrm->CRM_DATE;
            $crm_subject      = $rowEditCrm->crm_subject;
            $crm_crdate       = $rowEditCrm->CRM_CRDATE;
            $crm_type         = $rowEditCrm->crm_type;
            $crm_note         = $rowEditCrm->crm_note;
            $crm_flag         = $rowEditCrm->crm_flag;
            $crm_action       = $rowEditCrm->crm_action;
        }

        $stmt->close();

    } elseif ($mode === "Delete") {
        $stmt = $conn->prepare("DELETE FROM partners_crm WHERE crm_id = ?");
        $stmt->bind_param("s", $crm_id);
        $stmt->execute();
        $stmt->close();

        $errCode = 6;

    } elseif ($mode === "changeStat") {
        $changeTo = filter_input(INPUT_GET, 'changeTo', FILTER_SANITIZE_STRING);

        $stmt = $conn->prepare("UPDATE partners_crm SET crm_status = ? WHERE crm_id = ?");
        $stmt->bind_param("ss", $changeTo, $crm_id);
        $stmt->execute();
        $stmt->close();
    }
}

# end here
#=======================================================================

#=======================================================================
# getting all affilaites linked to through the site from the table

    $sql = "SELECT * FROM partners_affiliate ORDER BY affiliate_firstname DESC";
    $ret = mysqli_query($conn, $sql) or die(mysqli_error($conn));

# ends herre
#=======================================================================


#=======================================================================
# generating a javascript global array REGION
# which contains the categories of selected affiliates
# after executing the script will look like

# script starts here
# var REGION = new Array ()
# fiunction loadcat()
# {
#   REGION[affilaite id] = category name;
# }
# scipt ends here

     echo "<script type=\"text/javascript\">
    var REGION = new Array();
    REGION[0] = '';
";

if (mysqli_num_rows($ret) > 0) {
    echo "function loadCat() {\n";

    while ($row = mysqli_fetch_assoc($ret)) {
        $fld = addslashes($row['affiliate_category']);
        $pos = (int)$row['affiliate_id'];
        echo "    REGION[$pos] = '$fld';\n";
    }

    echo "
        var id = document.crmForm.crm_affiliate_list.value;
        document.crmForm.crm_cat_id.value = REGION[id];
        document.crmForm.crm_cat_list.value = REGION[id];
    }\n";
}

echo "</script>";

# script generation ends here
#=======================================================================


#=======================================================================
# setting err messages

# setting errMsgs Accroding to err code
    switch ($errCode) {
    case '1':
        $msg = "Please don't leave any fields empty.";
        break;

    case '2':
        $msg = "Please enter a valid date.";
        break;

    case '3':
        $msg  = "Sorry, there is already an entry for this merchant–affiliate combination.<br/>";
        $msg .= "Please try another combination or edit the existing one.";
        break;

    case '4':
        $msg = "Record has been inserted successfully.";
        break;

    case '5':
        $msg = "Record has been edited successfully.";
        break;

    case '6':
        $msg = "Record has been deleted successfully.";
        break;

    default:
        $msg = "";
        break;
}
# setting err messages   ends here
#=======================================================================


#=======================================================================
# Getting all crm modules added for seleted merchant

    $sqlCrm = "
    SELECT 
        A.affiliate_firstname, 
        A.affiliate_lastname, 
        C.crm_id, 
        C.crm_flag, 
        C.crm_type,
        C.crm_date, 
        DATE_FORMAT(C.crm_date, '%d/%m/%Y') AS CRM_DATE, 
        DATE_FORMAT(C.crm_crdate, '%d/%m/%Y') AS CRM_CRDATE, 
        C.crm_status, 
        C.crm_subject 
    FROM partners_crm AS C
    JOIN partners_affiliate AS A ON A.affiliate_id = C.crm_affiliateid
    WHERE C.crm_merchantid = ?
";

// Append filter for status
if (!empty($list_by)) {
    $sqlCrm .= " AND C.crm_status LIKE ?";
}

// Append sorting
if (!empty($order_by) && in_array($order_by, ['affiliate_firstname', 'affiliate_lastname', 'crm_flag', 'crm_type', 'crm_date', 'crm_status', 'crm_subject'])) {
    $order_seq = (strtoupper($order_seq) === 'DESC') ? 'DESC' : 'ASC';
    $sqlCrm .= " ORDER BY $order_by $order_seq";
}

$stmt = $conn->prepare($sqlCrm);

if (!empty($list_by)) {
    $like_status = '%' . $list_by . '%';
    $stmt->bind_param("ss", $crm_merchant_id, $like_status);
} else {
    $stmt->bind_param("s", $crm_merchant_id);
}

$stmt->execute();
$retCrm = $stmt->get_result();

#ends here
#=======================================================================


?>
<iframe width="168" height="175" name="gToday:normal:agenda.js" id="gToday:normal:agenda.js"
    src="../cal/ipopeng.htm" scrolling="no" frameborder="0"
    style="border:2px ridge; visibility:visible; z-index:999; position:absolute; left:-500px; top:0px;">
</iframe>

<br>

<!-- CRM Header Message -->
<table width="90%" class="tablewbdr" align="center">
    <tr>
        <td height="25" colspan="2" class="tdhead heading-3" style="text-align:center">
            Customer Relationship Module
        </td>
    </tr>
    <tr>
        <td height="10" colspan="2" class="textred" align="center">
            <?= htmlspecialchars($msg ?? '', ENT_QUOTES, 'UTF-8') ?>
        </td>
    </tr>
</table>

<!-- CRM Form -->
<form name="crmForm" action="crm_validate.php" method="post">
    <table width="90%" class="tablebdr" align="center" cellpadding="2" cellspacing="2">
        <tr class="grid1">
            <td height="25" align="right" width="40%">Affiliate Name</td>
            <td height="25" align="left">
                <select size="1" name="crm_affiliate_list" onchange="getCategory()">
                    <option value="0">Select Affiliate</option>
                    <?php
                    // Fetch affiliates again (modernized query if not already available)
                    $sqlAffiliates = "SELECT affiliate_id, affiliate_firstname, affiliate_lastname FROM partners_affiliate ORDER BY affiliate_firstname DESC";
                    $result = $conn->query($sqlAffiliates);
                    if ($result && $result->num_rows > 0) {
                        while ($row = $result->fetch_assoc()) {
                            $selected = ($crm_affiliate_id == $row['affiliate_id']) ? 'selected' : '';
                            $fullname = htmlspecialchars($row['affiliate_firstname'] . ' ' . $row['affiliate_lastname'], ENT_QUOTES, 'UTF-8');
                            echo "<option value=\"{$row['affiliate_id']}\" $selected>$fullname</option>";
                        }
                    }
                    ?>                     #=======================================================================
                     # populating affiliates list
                     #=======================================================================
                     <?php
require_once "../includes/dbconnect.php"; // Update this path if necessary

// Initialization
$msg = '';
$crm_affiliate_id = $_POST['crm_affiliate_list'] ?? 0;
$crm_subject = $_POST['crm_subject'] ?? '';
$crm_type = $_POST['crm_type'] ?? '';
$crm_crdate = $_POST['crm_crdate'] ?? '';
$crm_date = $_POST['crm_date'] ?? '';
$crm_note = $_POST['crm_note'] ?? '';
$crm_flag = $_POST['crm_flag'] ?? '';
$crm_merchant_id = $_POST['crm_merchant_id'] ?? '';
$crm_id = $_POST['crm_id'] ?? '';
$butCaption = isset($_POST['crm_id']) ? "Update" : "Add";

// Fetch affiliate list
$affiliates = [];
$query = "SELECT affiliate_id, affiliate_firstname, affiliate_lastname FROM affiliates ORDER BY affiliate_firstname ASC";
if ($result = $conn->query($query)) {
    while ($row = $result->fetch_assoc()) {
        $affiliates[] = $row;
    }
    $result->free();
}

// Fetch CRM records for listing
$order_By = $_GET['order_By'] ?? 'C.crm_date';
$order_seq = $_GET['order_seq'] ?? 'desc';
$list_by = $_GET['list_By'] ?? 'show';

$orderByValue = ($order_seq === 'asc') ? 'desc' : 'asc';

$crmList = [];
$sql = "
    SELECT C.*, A.affiliate_firstname, A.affiliate_lastname
    FROM crm C
    JOIN affiliates A ON C.crm_affiliate_id = A.affiliate_id
    WHERE C.crm_status = ?
    ORDER BY $order_By $order_seq
";

if ($stmt = $conn->prepare($sql)) {
    $stmt->bind_param("s", $list_by);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        $crmList[] = $row;
    }
    $stmt->close();
}
?>

<iframe width="168" height="175" name="gToday:normal:agenda.js" id="gToday:normal:agenda.js"
        src="../cal/ipopeng.htm" scrolling="no" frameborder='0'
        style="border:2px ridge; visibility:visible; z-index:999; position:absolute; left:-500px; top:0px;">
</iframe>

<br/>

<table width="90%" class="tablewbdr" align="center">
    <tr>
        <td height="25" colspan="2" class="tdhead heading-3" style="text-align:center"> Customer Relationship Module </td>
    </tr>
    <tr>
        <td height="10" colspan="2" class="textred" align="center"><?php echo htmlspecialchars($msg); ?></td>
    </tr>
</table>

<form name="crmForm" action="crm_validate.php" method="post">
    <table width="90%" class="tablebdr" align="center" cellpadding="2" cellspacing="2">
        <tr class="grid1">
            <td align="right" width="40%">Affiliate Name</td>
            <td align="left">
                <select name="crm_affiliate_list" onchange="getCategory()">
                    <option value="0">Select Affiliate</option>
                    <?php foreach ($affiliates as $aff): ?>
                        <option value="<?php echo $aff['affiliate_id']; ?>"
                            <?php if ($aff['affiliate_id'] == $crm_affiliate_id) echo "selected"; ?>>
                            <?php echo htmlspecialchars($aff['affiliate_firstname'] . ' ' . $aff['affiliate_lastname']); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </td>
        </tr>
        <tr class="grid2">
            <td align="right">Category</td>
            <td align="left">
                <input name="crm_cat_list" type="text" readonly="readonly"/>
                <input name="crm_cat_id" type="hidden"/>
            </td>
        </tr>
        <tr class="grid2">
            <td align="right">Subject</td>
            <td align="left"><input name="crm_subject" type="text" value="<?php echo htmlspecialchars($crm_subject); ?>"/></td>
        </tr>
        <tr class="grid2">
            <td align="right">Correspondence Type</td>
            <td align="left"><input name="crm_type" type="text" value="<?php echo htmlspecialchars($crm_type); ?>"/></td>
        </tr>
        <tr class="grid1">
            <td align="right">Correspondence Date</td>
            <td align="left">
                <input type="text" name="crm_crdate" size="10" value="<?php echo htmlspecialchars($crm_crdate); ?>"/>
                <a href="javascript:void(0)" onclick="cr_date(); return false;"><img src="../images/calbtn.gif" width="34" height="22" border='0'/></a>
            </td>
        </tr>
        <tr class="grid1">
            <td align="right">Action Date</td>
            <td align="left">
                <input type="text" name="crm_date" size="10" value="<?php echo htmlspecialchars($crm_date); ?>"/>
                <a href="javascript:void(0)" onclick="from_date(); return false;"><img src="../images/calbtn.gif" width="34" height="22" border='0'/></a>
            </td>
        </tr>
        <tr class="grid1">
            <td align="right">Note</td>
            <td align="left"><textarea name="crm_note" rows="5" cols="20"><?php echo htmlspecialchars($crm_note); ?></textarea></td>
        </tr>
        <tr class="grid2">
            <td align="right">Flag</td>
            <td align="left">
                <select name="crm_flag">
                    <option value="high" <?php if ($crm_flag == "high") echo "selected"; ?>>High</option>
                    <option value="medium" <?php if ($crm_flag == "medium") echo "selected"; ?>>Medium</option>
                    <option value="low" <?php if ($crm_flag == "low") echo "selected"; ?>>Low</option>
                </select>
            </td>
        </tr>
    </table>

    <table width="90%" class="tablewbdr" align="center">
        <tr>
            <td colspan="2" height="10"></td>
        </tr>
        <tr>
            <td colspan="2" align="center" class="tdhead">
                <input type="hidden" name="crm_merchant_id" value="<?php echo htmlspecialchars($crm_merchant_id); ?>"/>
                <input type="hidden" name="crm_id" value="<?php echo htmlspecialchars($crm_id); ?>"/>
                <input type="submit" name="crm_action" value="<?php echo htmlspecialchars($butCaption); ?>"/>
                &nbsp;&nbsp;&nbsp;
                <input type="reset" value="Cancel"/>
            </td>
        </tr>
    </table>
</form>

<br/>

<!-- CRM Filters -->
<table width="60%" class="tablebdr" align="center">
    <tr><td align="center"><a href="index.php?Act=list_crm"><span class="textred">All</span></a> - <b>Show All</b></td></tr>
    <tr><td align="center"><a href="index.php?Act=list_crm&amp;list_By=show"><span class="textred">Active</span></a> - <b>Show Only Active CRM</b></td></tr>
    <tr><td align="center"><a href="index.php?Act=list_crm&amp;list_By=hide"><span class="textred">Hidden</span></a> - <b>Show Only Hidden CRM</b></td></tr>
</table>
<br/>

<!-- CRM Record List -->
<?php if (count($crmList) > 0): ?>
    <table width="98%" class="tablebdr" align="center">
        <tr class="tdhead">
            <td align="center" width="20%">
                <a href="index.php?Act=list_crm&amp;order_By=A.affiliate_firstname&amp;order_seq=<?php echo $orderByValue; ?>&amp;list_By=<?php echo $list_by; ?>">
                    <img src="<?php echo ($order_By == 'A.affiliate_firstname') ? $image : $imageNormal; ?>" width="10" height="10"/> Affiliate
                </a>
            </td>
            <td align="center" width="20%">
                <a href="index.php?Act=list_crm&amp;order_By=C.crm_subject&amp;order_seq=<?php echo $orderByValue; ?>&amp;list_By=<?php echo $list_by; ?>">
                    <img src="<?php echo ($order_By == 'C.crm_subject') ? $image : $imageNormal; ?>" width="10" height="10"/> Subject
                </a>
            </td>
            <td align="center" width="10%">Type</td>
            <td align="center" width="10%">Date</td>
            <td align="center" width="10%">Cr. Date</td>
            <td align="center" width="10%">Flag</td>
            <td align="center" width="20%">Action</td>
        </tr>
        <?php foreach ($crmList as $index => $row): 
            $affil_name = htmlspecialchars(trim($row['affiliate_firstname']) . ' ' . trim($row['affiliate_lastname']));
            $flag = htmlspecialchars(ucwords(strtolower($row['crm_flag'])));
            $editStatus = ($row['crm_status'] == 'show') ? 'hide' : 'show';
        ?>
            <tr class="<?php echo ($index % 2 == 0) ? 'grid1' : 'grid2'; ?>">
                <td align="center"><?php echo $affil_name; ?></td>
                <td align="center"><?php echo htmlspecialchars($row['crm_subject']); ?></td>
                <td align="center"><?php echo htmlspecialchars($row['crm_type']); ?></td>
                <td align="center"><?php echo htmlspecialchars($row['crm_date']); ?></td>
                <td align="center"><?php echo htmlspecialchars($row['crm_crdate']); ?></td>
                <td align="center"><?php echo $flag; ?></td>
                <td align="center">
                    <a href="index.php?Act=list_crm&amp;mode=Edit&amp;id=<?php echo $row['crm_id']; ?>">Edit</a> ||
                    <a href="index.php?Act=list_crm&amp;mode=Delete&amp;id=<?php echo $row['crm_id']; ?>" onclick="return deleteConfirm()">Delete</a> ||
                    <a href="index.php?Act=list_crm&amp;mode=changeStat&amp;id=<?php echo $row['crm_id']; ?>&amp;changeTo=<?php echo $editStatus; ?>"><?php echo ucfirst($editStatus); ?></a>
                </td>
            </tr>
        <?php endforeach; ?>
    </table>
<?php else: ?>
    <p class="textred" align="center">Sorry, No Records Found</p>
<?php endif; ?>

<!-- JS Functions -->
<script type="text/javascript">
    function deleteConfirm() {
        return confirm("Do you really want to Delete?");
    }

    function getCategory() {
        const id = document.crmForm.crm_affiliate_list.value;
        document.crmForm.crm_cat_id.value = REGION[id];
        document.crmForm.crm_cat_list.value = REGION[id];
    }

    function from_date() {
        gfPop.fStartPop(document.crmForm.crm_date, Date);
    }

    function cr_date() {
        gfPop.fStartPop(document.crmForm.crm_crdate, Date);
    }
</script>
