Get_order_manually.gs

[puwertique]

const WEBHOOK_NAME = "orders"; // اسم Sheet
const WC_SITE_URL = "https://acctopix.com"; // رابط المتجر

function fetchOrdersManually() {
const scriptProperties = PropertiesService.getScriptProperties();
const WC_CONSUMER_KEY = scriptProperties.getProperty("acctopix_CONSUMER_KEY");
const WC_CONSUMER_SECRET = scriptProperties.getProperty("acctopix_CONSUMER_SECRET");

if (!WC_CONSUMER_KEY || !WC_CONSUMER_SECRET) {
Logger.log("❌ API keys are missing in Script Properties");
return;
}

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(WEBHOOK_NAME);
if (!sheet) {
Logger.log("❌ Sheet not found: " + WEBHOOK_NAME);
return;
}

// Headers بالترتيب المطلوب + CRM_ID
const headers = [
"CRM_ID",
"ID","Store Name","ip_address","source","info device",
"Date Created","date modified","Billing Name","Billing Email","Billing Phone",
"Device Streaming","Adult Content","Package","Total","Currency",
"Products","mac mag box","app","mac adress APP","KEY APP",
"servar","user panel","M3U LINK","user","pass",
"server","Subscription date","expiration date","note","bank account name",
"Status"
];

// إضافة Headers إذا لم تكن موجودة
if (sheet.getLastRow() === 0) {
sheet.appendRow(headers);
}

// 🔽 جلب كل الطلبات من WooCommerce عبر الصفحات
let page = 1;
let allOrders = [];

while (true) {
const url = ${WC_SITE_URL}/wp-json/wc/v3/orders?per_page=50&page=${page}&consumer_key=${WC_CONSUMER_KEY}&consumer_secret=${WC_CONSUMER_SECRET};
const response = UrlFetchApp.fetch(url);
const orders = JSON.parse(response.getContentText());

if (orders.length === 0) break;

allOrders = allOrders.concat(orders);
page++;

}

// 🔽 جلب IDs الموجودة مسبقًا في العمود 2 (ID المتجر)
let existingIds = [];
if (sheet.getLastRow() > 1) {
existingIds = sheet.getRange(2, 2, sheet.getLastRow() - 1, 1)
.getValues()
.flat();
}

// 🔽 فلترة الطلبات الجديدة فقط
let newOrders = allOrders.filter(order => !existingIds.includes(order.id));

if (newOrders.length === 0) {
Logger.log("ℹ No new orders to add.");
return;
}

// 🔽 ترتيب الطلبات الجديدة من الأقدم للأحدث حسب Date Created
newOrders.sort((a, b) => new Date(a.date_created) - new Date(b.date_created));

let newRows = [];

newOrders.forEach(order => {

const crmId = generateCRMID(); // توليد CRM_ID داخلي
const billing = order.billing || {};
const meta = order.meta_data || [];

let device_streaming="", adult_content="", select_package="", source="";

meta.forEach(m=>{
  const value = m.value || "";
  switch(m.key){
    case "device_streaming": device_streaming = value; break;
    case "adult_content": adult_content = value; break;
    case "select_package": select_package = value; break;
    case "_wc_order_attribution_utm_source": source = value; break;
  }
});

const product_names = order.line_items ? order.line_items.map(i=>i.name).join(", ") : "";
let storeName = "";
try { storeName = extractDomainFromUrl(order._links.self[0].href); } catch(e){}

newRows.push([
  crmId,
  order.id,
  storeName,
  order.customer_ip_address || "",
  source,
  order.customer_user_agent || "",
  order.date_created ? new Date(order.date_created) : "",
  order.date_modified ? new Date(order.date_modified) : "",
  `${billing.first_name || ""} ${billing.last_name || ""}`,
  billing.email || "",
  billing.phone || "",
  device_streaming,
  adult_content,
  select_package,
  order.total || "",
  order.currency || "",
  product_names,
  "", "", "", "", "", "", "", "", "", "",
  "", "", "", "", "",
  order.status || ""
]);

});

// 🔽 إدخال الطلبات الجديدة دفعة واحدة في أول سطر فارغ
sheet.getRange(sheet.getLastRow() + 1, 1, newRows.length, newRows[0].length)
.setValues(newRows);

Logger.log(✅ ${newRows.length} new orders added.);
}

// 🔹 دالة توليد CRM_ID داخلي
function generateCRMID() {
const props = PropertiesService.getScriptProperties();
let lastId = parseInt(props.getProperty("LAST_CRM_ID") || "0", 10);

lastId++;
props.setProperty("LAST_CRM_ID", lastId.toString());

return "CRM-" + Utilities.formatString("%06d", lastId);
}

// 🔹 دالة استخراج اسم المتجر من URL
function extractDomainFromUrl(url) {
try {
return url.replace(/^https?:///, "").split("/")[0];
} catch(e){
return "";
}
}