<?php
ini_set("display_errors", "1");
ini_set("display_startup_errors", "1");
error_reporting(E_ALL);
class Upload_hs extends MY_Controller
{
  var $base_url;
  function __construct()
  {
    parent::__construct();
    $this->debug = false;
    $this->base_url = "https://mobile.pramita.co.id/";
  }
  function now()
  {
    return Date("Y-m-d H:i:s");
  }
  function log($msg)
  {
    echo "{$this->now()} $msg\n";
  }
  public function get_branch_default()
  {
    $sql =
      "select * from m_branch where M_BranchIsDefault = 'Y' and M_BranchIsActive = 'Y'";
    $qry = $this->db->query($sql);
    if (!$qry) {
      echo "{$this->now()} ERR : {$this->db->error()["message"]}\n";
      exit();
    }
    $rows = $qry->result_array();
    if (count($rows) == 0) {
      echo "{$this->now()} ERR  Get Default Branch: {$this->db->error()["message"]}\n";
      exit();
    }
    $branchID = $rows[0]["M_BranchID"];
    $branchCode = $rows[0]["M_BranchCode"];

    return [$branchID, $branchCode];
  }

  function order_quota($date = "")
  {
    if ($date == "") {
      $date = date("Y-m-d");
    }

    $last_updated = $this->get_last_upload("order_quota");
    $this->db->trans_begin();
    $this->log("Start Upload Order Quota, last updated [${last_updated}]");
    list($branchID, $branchCode) = $this->get_branch_default();

    $sql_online = "SELECT 
                ? as M_BranchID,
                ? as M_BranchCode,
                t_order.*
                FROM one_hs.t_order
                WHERE T_OrderIsActive = 'N' 
                AND T_OrderLastUpdated >= ?
                AND T_OrderDate >= ?
                AND T_OrderT_TransactionOldID IS NOT NULL";
    $qry_online = $this->db->query($sql_online, array($branchID, $branchCode, $last_updated, $date));
    if ($qry_online) {
      $rows_online = $qry_online->result_array();
    } else {
      $this->sys_error_db("local online error", $this->db);
      exit;
    }

    $sql_offline = "SELECT 
                ? as M_BranchID,
                ? as M_BranchCode,
                t_order.*
                FROM one_hs.t_order
                WHERE T_OrderDate >= ? 
                AND T_OrderLastUpdated >= ?
                AND T_OrderT_TransactionOldID IS NULL";

    $qry_offline = $this->db->query($sql_offline, array($branchID, $branchCode, $date, $last_updated));
    if ($qry_offline) {
      $rows_offline = $qry_offline->result_array();
    } else {
      $this->sys_error_db("local offline error", $this->db);
      exit;
    }
    $param = json_encode(["online" => $rows_online, "offline" => $rows_offline]);
    $zparam = gzcompress($param);

    $url = $this->base_url . "one-api/hs/r_hs/update_order_quota";
    $resp = $this->post($url, $zparam);
    if ($resp["status"] != "OK") {
      $this->log("Error Upload Order Quota " . $resp["message"]);
      $this->db->trans_rollback();
      exit();
    }
    $this->log("Upload Order Quota [OK]");
    $this->update_last_upload("order_quota");
    $this->db->trans_commit();
  }
  function receive($trxID = "")
  {
    $last_updated = $this->get_last_upload("confirm_receive");
    $this->db->trans_begin();
    $this->log(
      "Start Upload Confirm Received, last updated [${last_updated}]"
    );
    //$last_updated = "2022-01-01 00:01:01";
    if ($trxID != "") {
      $sql = "select * 
              from one_hs.t_transaction
              where T_TransactionIsConfirmReceived = 'Y'
              and T_TransactionIsActive = 'Y'
              and T_TransactionOldID is not null 
              and T_TransactionOldID = ? ";
      $qry = $this->db->query($sql, [$trxID]);
    } else {
      $sql = "select * 
          from one_hs.t_transaction
          where T_TransactionIsConfirmReceived = 'Y'
          and T_TransactionIsActive = 'Y'
          and T_TransactionOldID is not null 
          and T_TransactionOldID > 0
          and T_TransactionLastUpdated > ?";
      $qry = $this->db->query($sql, [$last_updated]);
    }
    if (!$qry) {
      $this->log(
        " Error Get Transaction : " . $this->db->error()["message"]
      );
      $this->db->trans_rollback();
      exit();
    }
    $rows = $qry->result_array();
    $t_ids = "";
    $t_no = "";
    foreach ($rows as $r) {
      if ($t_ids != "") {
        $t_ids .= ",";
        $t_no .= ",";
      }
      $t_ids .= $r["T_TransactionID"];
      $t_no .= $r["T_TransactionNumbering"];
    }
    if (count($rows) == 0) {
      $this->log("\tNo Pending Confirm Received");
      $this->update_last_upload("confirm_receive");
      exit();
    }
    $transactions = $rows;
    $this->log("\tUploading $t_no ");

    $sql = "select * 
        from one_hs.f_payment
        where F_PaymentIsActive='Y' and F_PaymentT_TransactionID in ($t_ids)";

    $qry = $this->db->query($sql);
    if (!$qry) {
      $this->log(" Error Get Payment: " . $this->db->error()["message"]);
      $this->db->trans_rollback();
      exit();
    }
    $payments = $qry->result_array();

    $sql = "select * 
        from one_hs.t_order 
        where T_OrderIsActive='Y' and T_OrderT_TransactionID in ($t_ids)";

    $qry = $this->db->query($sql);
    if (!$qry) {
      $this->log(" Error Get Order : " . $this->db->error()["message"]);
      $this->db->trans_rollback();
      exit();
    }
    $orders = $qry->result_array();
    if (count($orders) == 0) {
      $this->log("\tNo Pending Order");
      exit();
    }

    $sql = "select t_orderdetail.* 
        from one_hs.t_orderdetail 
        join one_hs.t_order on T_OrderDetailT_OrderID = T_OrderID 
        and T_OrderIsActive='Y' 
        and T_OrderT_TransactionID in ($t_ids)";

    $qry = $this->db->query($sql);
    if (!$qry) {
      $this->log(" Error Get Order : " . $this->db->error()["message"]);
      $this->db->trans_rollback();
      exit();
    }
    $details = $qry->result_array();
    if (count($details) == 0) {
      $this->log("\tNo Pending Order Detail");
      exit();
    }

    $sql = "select t_ordersample.* 
        from one_hs.t_ordersample
        join one_hs.t_order on T_OrderSampleT_OrderID = T_OrderID 
        and T_OrderIsActive='Y' 
        and T_OrderT_TransactionID in ($t_ids)";

    $qry = $this->db->query($sql);
    if (!$qry) {
      $this->log(" Error Get sample : " . $this->db->error()["message"]);
      $this->db->trans_rollback();
      exit();
    }
    $samples = $qry->result_array();

    $sql = "select t_orderdeliveries.* 
        from one_hs.t_orderdeliveries
        join one_hs.t_order on T_OrderDeliveriesT_OrderID = T_OrderID 
        and T_OrderIsActive='Y' 
        and T_OrderT_TransactionID in ($t_ids)";

    $qry = $this->db->query($sql);
    if (!$qry) {
      $this->log(
        " Error Get Delivery : " . $this->db->error()["message"]
      );
      $this->db->trans_rollback();
      exit();
    }
    $deliveries = $qry->result_array();
    //m_patient
    $sql = "select m_patient.* 
        from one_hs.m_patient
        join one_hs.t_order on T_OrderM_PatientID = M_PatientID
        and T_OrderIsActive='Y' 
        and T_OrderT_TransactionID in ($t_ids)";

    $qry = $this->db->query($sql);
    if (!$qry) {
      $this->log(
        " Error Get Delivery : " . $this->db->error()["message"]
      );
      $this->db->trans_rollback();
      exit();
    }
    $patients = $qry->result_array();

    //populate orders
    foreach ($orders as $idx => $o) {
      $x_detail = array_filter($details, function ($d) use ($o) {
        return $o["T_OrderID"] == $d["T_OrderDetailT_OrderID"];
      });
      $orders[$idx]["detail"] = array_values($x_detail);

      $x_sample = array_filter($samples, function ($d) use ($o) {
        return $o["T_OrderID"] == $d["T_OrderSampleT_OrderID"];
      });
      $orders[$idx]["sample"] = array_values($x_sample);

      $x_delivery = array_filter($deliveries, function ($d) use ($o) {
        return $o["T_OrderID"] == $d["T_OrderDeliveriesT_OrderID"];
      });
      $orders[$idx]["delivery"] = array_values($x_delivery);

      $x_patient = array_filter($patients, function ($p) use ($o) {
        return $o["T_OrderM_PatientID"] == $p["M_PatientID"];
      });
      $orders[$idx]["patient"] = array_values($x_patient);
    }
    foreach ($transactions as $idx => $t) {
      $x_order = array_filter($orders, function ($o) use ($t) {
        return $t["T_TransactionID"] == $o["T_OrderT_TransactionID"];
      });
      $transactions[$idx]["order"] = array_values($x_order);
      $x_payment = array_filter($payments, function ($o) use ($t) {
        return $t["T_TransactionID"] == $o["F_PaymentT_TransactionID"];
      });
      $transactions[$idx]["payment"] = array_values($x_payment);
    }

    $param = ["tx" => $transactions];
    $zparam = gzcompress(json_encode($param));
    $url = $this->base_url . "one-api/hs/r_hs/update_receive";
    $resp = $this->post($url, $zparam);
    if ($resp["status"] != "OK") {
      $this->log("Error Upload Receive " . $resp["message"]);
      $this->db->trans_rollback();
      exit();
    }
    $this->log("Upload Receive [OK]");
    $this->update_last_upload("confirm_receive");
    $this->db->trans_commit();
  }
  function update_last_upload($code)
  {
    $fname = "/xtmp/hs-upload-status.json";
    $now = Date("Y-m-d H:i:s");
    if (file_exists($fname)) {
      $status = json_decode(file_get_contents($fname), true);
      $status[$code] = $now;
      $j_status = json_encode($status);
      $w_status = file_put_contents($fname, $j_status);
      if ($w_status === false) {
        echo "Write Error";
      }
    } else {
      $status[$code] = $now;
      $j_status = json_encode($status);
      file_put_contents($fname, $j_status);
    }
  }
  function get_last_upload($code)
  {
    $now = Date("Y-m-d H:i:s");
    if (file_exists("/xtmp/hs-upload-status.json")) {
      $status = json_decode(
        file_get_contents("/xtmp/hs-upload-status.json"),
        true
      );
      if (isset($status[$code])) {
        $result = $status[$code];
        $status[$code] = $now;
        file_put_contents("/xtmp/hs-upload-status.json", json_encode($status));
        return $result;
      }
      return "2023-03-01 00:00:01";
    } else {
      file_put_contents("/xtmp/hs-upload-status.json", json_encode([$code => date("Y-m-d h:i:s")]));
      return "2023-03-01 00:00:01";
    }
  }
  function old_get_last_upload($code)
  {
    $now = Date("Y-m-d H:i:s");
    if (file_exists("/xtmp/hs-upload-status.json")) {
      $status = json_decode(
        file_get_contents("/xtmp/hs-upload-status.json"),
        true
      );
      if (isset($status[$code])) {
        $result = $status[$code];
        $status[$code] = $now;
        file_put_contents(
          "/xtmp/hs-upload-status.json",
          json_encode($status)
        );
        return $result;
      }
      $status[$code] = $now;
      file_put_contents(
        "/xtmp/hs-upload-status.json",
        json_encode($status)
      );
      return $now;
    } else {
      $status[$code] = $now;
      file_put_contents(
        "/xtmp/hs-upload-status.json",
        json_encode($status)
      );
      return "2022-01-01 00:00:01";
    }
  }
  function surat_jalan()
  {
    $last_update = $this->get_last_upload("surat_jalan");
    $sql = "select  HS_DeliveryOrderDetailLastUpdated HS_InfoSjLastUpdated, 
        T_TransactionOldID HS_InfoSjT_TransactionID, 
        ifnull(Nat_StaffName,M_StaffName) HS_InfoSjStaffName,
        M_UserUsername HS_InfoSjUsername
        from 
        one_hs.hs_deliveryorderdetail 
        join one_hs.hs_deliveryorder on HS_DeliveryOrderDetailHS_DeliveryOrderID = HS_DeliveryOrderID
          and HS_DeliveryOrderDetailIsActive = 'Y'
          and HS_DeliveryOrderIsActive = 'Y'
          and HS_DeliveryOrderLastUpdated > ?
        join one_hs.t_transaction on HS_DeliveryOrderDetailT_TransactionID = T_TransactionID
          and T_TransactionOldID is not null and T_TransactionOldID > 0
        join m_user on HS_DeliveryOrderM_UserID = M_UserID 
        left join m_staff on M_UserM_StaffID = M_StaffID
        left join nat_staff on M_UserM_StaffID = Nat_StaffID";
    $qry = $this->db->query($sql, [$last_update]);

    if (!$qry) {
      $this->log(
        "Error Upload SJ info: " . $this->db->error()["message"]
      );
      $this->db->trans_rollback();
      exit();
    }
    $rows = $qry->result_array();
    if (count($rows) == 0) {
      $this->log("No Pending Info Surat Jalan, Last Upload $last_update");
      $this->db->trans_rollback();
      exit();
    }
    $param = ["sj" => $rows];
    $zparam = gzcompress(json_encode($param));
    $url = $this->base_url . "one-api/hs/r_hs/update_sj";
    $resp = $this->post($url, $zparam);
    if ($resp["status"] != "OK") {
      $this->log("Error Upload SJ info " . $resp["message"]);
      $this->db->trans_rollback();
      exit();
    }
    $this->update_last_upload("surat_jalan");
    $this->log("Upload SJ [OK]");
  }
  public function index()
  {
    //hs_upload
    $this->db->trans_begin();
    $sql =
      "select * from hs_upload where Hs_UploadIsActive='Y' and Hs_UploadIsSent ='N' limit 0,1";
    $qry = $this->db->query($sql);
    if (!$qry) {
      $this->log("Error hs_upload : " . $this->db->error()["message"]);
      $this->db->trans_rollback();
      exit();
    }
    if (count($qry->result_array()) == 0) {
      $this->log("No Pending hs_upload.");
      $this->db->trans_rollback();
      exit();
    }
    //branch
    $sql =
      "select * from m_branch where M_BranchIsActive='Y' and M_BranchIsDefault='Y'";
    $qry = $this->db->query($sql);
    if (!$qry) {
      echo "{$this->now()} ERR : {$this->db->error()["message"]}\n . {$this->db->last_query}";
      exit();
    }
    $rows = $qry->result_array();
    if (count($rows) == 0) {
      $this->log("ERR : No Default Branch");
      exit();
    }
    $branchID = $rows[0]["M_BranchID"];
    $branchCode = $rows[0]["M_BranchCode"];
    //one_hs.hs_config
    //one_hs.hs_price
    //one_hs.hs_test
    //one_hs.hs_schedule
    //one_hs.hs_scheduledetail
    $sql =
      "select ? HS_ConfigM_BranchID, ? HS_ConfigM_BranchCode, hs_config.* from one_hs.hs_config";
    $qry = $this->db->query($sql, [$branchID, $branchCode]);
    if (!$qry) {
      echo "{$this->now()} ERR hs_config: {$this->db->error()["message"]}\n . {$this->db->last_query()}";
      exit();
    }
    $hs_config = $qry->result_array();

    $sql = "select ? HS_PriceM_BranchID,  hs_price.*
        from one_hs.hs_price";
    $qry = $this->db->query($sql, [$branchID]);
    if (!$qry) {
      echo "{$this->now()} ERR hs_price: {$this->db->error()["message"]}\n . {$this->db->last_query()}";
      exit();
    }
    $hs_price = $qry->result_array();

    $sql = "select ? HS_TestM_BranchID, hs_test.*
        from one_hs.hs_test";
    $qry = $this->db->query($sql, [$branchID]);
    if (!$qry) {
      echo "{$this->now()} ERR hs_test : {$this->db->error()["message"]}\n . {$this->db->last_query()}";
      exit();
    }
    $hs_test = $qry->result_array();

    $sql = "select ? HS_TestM_BranchID, hs_test.*
        from one_hs.hs_test";
    $qry = $this->db->query($sql, [$branchID]);
    if (!$qry) {
      echo "{$this->now()} ERR hs_test : {$this->db->error()["message"]}\n . {$this->db->last_query()}";
      exit();
    }
    $hs_test = $qry->result_array();

    $sql = "select ? HS_ScheduleM_BranchID, ? HS_ScheduleM_BranchCode, hs_schedule.*
        from one_hs.hs_schedule";
    $qry = $this->db->query($sql, [$branchID, $branchCode]);
    if (!$qry) {
      echo "{$this->now()} ERR hs_schedule: {$this->db->error()["message"]}\n . {$this->db->last_query()}";
      exit();
    }
    $hs_schedule = $qry->result_array();

    $sql = "select ? HS_ScheduleDetailM_BranchID, ? HS_ScheduleDetailM_BranchCode, hs_scheduledetail.*
        from one_hs.hs_scheduledetail";
    $qry = $this->db->query($sql, [$branchID, $branchCode]);
    if (!$qry) {
      echo "{$this->now()} ERR hs_scheduledetail: {$this->db->error()["message"]}\n . {$this->db->last_query()}";
      exit();
    }
    $hs_scheduledetail = $qry->result_array();
    $data = [
      "hs_config" => $hs_config,
      "hs_test" => $hs_test,
      "hs_price" => $hs_price,
      "hs_schedule" => $hs_schedule,
      "hs_scheduledetail" => $hs_scheduledetail,
    ];
    $jdata = json_encode($data);
    $md5 = md5($jdata);
    $param = ["md5" => $md5, "data" => $data];
    $jparam = json_encode($param);
    $zparam = gzcompress($jparam);
    $size = round(strlen($zparam) / 1024, 2);
    $size_org = round(strlen($jparam) / 1024, 2);
    $this->log(
      "Upload HS Masterdata (compressed): " .
        $size_org .
        " => " .
        $size .
        " kB"
    );
    $url = "{$this->base_url}one-api/hs/r_hs";
    $resp = $this->post($url, $zparam);
    if ($resp["status"] == "ERR") {
      $this->log("Error Upload HS Masterdata " . $resp["message"]);
      $this->db->trans_rollback();
      exit();
    }
    $sql =
      "update hs_upload set HS_UploadIsSent='Y', HS_UploadSentDate = now() where Hs_UploadIsSent='N' and Hs_UploadIsActive='Y'";
    $qry = $this->db->query($sql);
    if (!$qry) {
      echo "{$this->now()} ERR update hs_upload : {$this->db->error()["message"]}\n . {$this->db->last_query()}";
      exit();
    }
    $this->db->trans_commit();
    $this->log("Upload HS MasterData [OK] " . json_encode($resp["result"]));
    $this->order_quota();
  }
  function sum_tx($date = "")
  {
    if ($date == "") {
      $date = date("Y-m-d");
      $time = date("H:00:00");
    } else {
      $date = date("Y-m-d", strtotime($date));
      $time = "00:00:00";
    }
    $this->db->trans_begin();
    $sql =
      "select * from m_branch where M_BranchIsActive='Y' and M_BranchIsDefault='Y'";
    $qry = $this->db->query($sql);
    if (!$qry) {
      echo "{$this->now()} ERR : {$this->db->error()["message"]}\n . {$this->db->last_query}";
      exit();
    }
    $rows = $qry->result_array();
    if (count($rows) == 0) {
      $this->log("ERR : No Default Branch");
      exit();
    }
    $branchID = $rows[0]["M_BranchID"];
    $branchCode = $rows[0]["M_BranchCode"];
    //one_hs.sum_tx
    $sql = "select ? HS_SumTxM_BranchID, ? HS_SumTxM_BranchCode,
        T_TransactionID HS_SumTxT_TransactionID, T_TransactionCreated HS_SumTxT_TrancactionCreated,
        T_OrderID HS_SumTxT_OrderID,
        T_TransactionIsActive HS_SumTxT_TransactionIsActive, 
        concat(T_OrderDate,' ',T_OrderTime) HS_SumTxT_OrderDateTime, 
        T_OrderNumber HS_SumTxT_OrderNumber, T_OrderIsActive HS_SumTxT_OrderIsActive
        from one_hs.t_transaction 
        join one_hs.t_order on  T_TransactionID = T_OrderT_TransactionID
        and T_OrderDate > ? and T_OrderTime > ?
        and ( T_TransactionOldID is null or T_TransactionOldID = 0 )
        ";
    $qry = $this->db->query($sql, [$branchID, $branchCode, $date, $time]);
    if (!$qry) {
      echo "{$this->now()} ERR hs_config: {$this->db->error()["message"]}\n . {$this->db->last_query()}";
      exit();
    }
    $hs_sum_tx = $qry->result_array();

    $data = [
      "hs_sum_tx" => $hs_sum_tx,
    ];
    $jdata = json_encode($data);
    $md5 = md5($jdata);
    $param = ["md5" => $md5, "data" => $data];
    $jparam = json_encode($param);
    $zparam = gzcompress($jparam);
    $size = round(strlen($zparam) / 1024, 2);
    $size_org = round(strlen($jparam) / 1024, 2);
    $this->log(
      "Upload HS Sum Transaction (compressed): " .
        $size_org .
        " => " .
        $size .
        " kB"
    );
    $url = "{$this->base_url}one-api/hs/r_hs";
    $resp = $this->post($url, $zparam);
    if ($resp["status"] == "ERR") {
      $this->log("Error Upload HS Sum Transaction " . $resp["message"]);
      $this->db->trans_rollback();
      exit();
    }
    $this->log(
      "Upload HS Sum Transaction [OK] " . json_encode($resp["result"])
    );
  }
  function post($url, $data)
  {
    $ch = curl_init($url);

    curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
    curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5);
    curl_setopt($ch, CURLOPT_TIMEOUT, 120);
    curl_setopt($ch, CURLOPT_HTTPHEADER, [
      "Content-Type: application/octet-stream",
      "Content-Length: " . strlen($data),
    ]);
    $result = curl_exec($ch);
    if (curl_errno($ch) > 0) {
      return [
        "status" => "ERR",
        "message" => curl_error($ch),
      ];
    }
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    if ($httpCode != 200) {
      return [
        "status" => "ERR",
        "message" => "Http Response : $httpCode",
      ];
    }
    $j_result = json_decode($result, true);
    if (!$j_result) {
      return [
        "status" => "ERR",
        "message" => "JSON invalid: $result",
      ];
    }
    return $j_result;
  }
}

/*
CREATE TABLE `hs_schedule` (
  `HS_ScheduleID` int(11) NOT NULL AUTO_INCREMENT,
  `HS_ScheduleM_BranchID` int(11) NOT NULL,
  `HS_ScheduleM_BranchCode` varchar(2),
  `HS_ScheduleM_RegDayID` int(11) NOT NULL,
  `HS_ScheduleLimit` int(11) NOT NULL DEFAULT 10,
  `HS_ScheduleIsActive` char(1) NOT NULL DEFAULT 'Y',
  `HS_ScheduleUserID` int(11) NOT NULL DEFAULT 3,
  `HS_ScheduleCreated` datetime NOT NULL DEFAULT current_timestamp(),
  `HS_ScheduleLastUpdated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`HS_ScheduleID`,HS_ScheduleM_BranchID),
  key(HS_ScheduleM_RegDayID),
  key(HS_ScheduleM_BranchCode)
);

CREATE TABLE `hs_scheduledetail` (
  `HS_ScheduleDetailID` int(11) NOT NULL AUTO_INCREMENT,
  `HS_ScheduleDetailM_BranchID` int(11) NOT NULL ,
  `HS_ScheduleDetailM_BranchCode` varchar(2) NOT NULL ,
  `HS_ScheduleDetailHS_ScheduleID` int(11) NOT NULL,
  `HS_ScheduleDetailM_RegTimeID` int(11) NOT NULL,
  `HS_ScheduleDetailIsActive` char(1) NOT NULL DEFAULT 'Y',
  `HS_ScheduleDetailUserID` int(11) NOT NULL DEFAULT 3,
  `HS_ScheduleDetailCreated` datetime NOT NULL DEFAULT current_timestamp(),
  `HS_ScheduleDetailLastUpdated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`HS_ScheduleDetailID`,HS_ScheduleDetailM_BranchID),
  KEY `HS_ScheduleDetailHS_ScheduleID` (`HS_ScheduleDetailHS_ScheduleID`),
  KEY `HS_ScheduleDetailHS_Time` (`HS_ScheduleDetailM_RegTimeID`)
);

drop table hs_sum_tx;
create table hs_sum_tx (
  HS_SumTxM_BranchID int,
  HS_SumTxM_BranchCode varchar(2),
  HS_SumTxT_TransactionID int,
  HS_SumTxT_TrancactionCreated datetime,
  HS_SumTxT_OrderDateTime datetime,
  HS_SumTxT_OrderID int,
  HS_SumTxT_OrderNumber varchar(50),
  HS_SumTxT_TransactionIsActive varchar(1),
  HS_SumTxT_OrderIsActive varchar(1),
  primary key (HS_SumTxM_BranchID,HS_SumTxT_TransactionID,HS_SumTxT_OrderID),
  key (HS_SumTxM_BranchID),
  key (HS_SumTxT_TransactionID),
  key (HS_SumTxT_TrancactionCreated),
  key (HS_SumTxT_OrderDateTime),
  key (HS_SumTxT_OrderIsActive),
  key (HS_SumTxT_TransactionIsActive)
);
*/