<?php
namespace App\Library;
use Illuminate\Support\Facades\Validator;
use Illuminate\Http\Request;
use DB;

class SfdbQueryMethod
{
    //Create Customer
    public function createCustomer($customerId)
    {
        $query = 'INSERT INTO salesforce.account(customer_type_id__c,customer_pan__c,email__c,crm_id__c,first_name__c,name,device_name__c,isdeleted,distributor_id__c,beat_plan__c,external_id__c,customer_code__c,assign_to__c,customer_gst__c,alternate_mobile__c,customer_profile_image__c,mobile__c,last_name__c,billingcity,title__c,billingpostalcode,billingstate,billingcountry,billingstreet,customer_id__c,status__c,description,sales_area__c,shippingstate,shippingstreet,price_book_id__c,type__c,parent_customer_id__c,whole_seller_id__c,primary_contact__c,shippingpostalcode,shippingcountry,payment_term_id__c,shippingcity)
        SELECT "CustomerTypeId","CustomerPAN","Email","CrmId","FirstName","CustomerName","DeviceName",CAST("IsDeleted" as BOOLEAN),"DistributorId","BeatPlan","CustomerId","CustomerCode","AssignTo","CustomerGST","AlternateMobile","CustomerProfileImage", "Mobile","LastName","BillingCity","Title","BillingPinCode","BillingState",public.getCountry("BillingCountry"),"BillingAddress","CustomerId","Status","Description", "SalesArea","ShippingState","ShippingAddress","PriceBookId","Type","ParentCustomerId","WholeSellerId","PrimaryContact","ShippingPinCode",public.getCountry(CAST("ShippingCountry" as INTEGER)), "PaymentTermId","ShippingCity" FROM public.tbl_customer selected WHERE selected."customerId" = $customerId';
        $result=DB::connection('pgsql')->insert($query);
        return  $result;
    }
    //Update Customer
    public function updateCustomer($customerId)
    {
        $query = 'UPDATE salesforce.account toUpdate SET customer_type_id__c=selected."CustomerTypeId",customer_pan__c=selected."CustomerPAN",email__c=selected."Email",crm_id__c=selected."CrmId",first_name__c=selected."FirstName",name=selected."CustomerName",device_name__c=selected."DeviceName",isdeleted=CAST(selected."IsDeleted" as BOOLEAN),distributor_id__c=selected."DistributorId",beat_plan__c=selected."BeatPlan",external_id__c=selected."CustomerId",customer_code__c=selected."CustomerCode",assign_to__c=selected."AssignTo",customer_gst__c=selected."CustomerGST",alternate_mobile__c=selected."AlternateMobile",customer_profile_image__c=selected."CustomerProfileImage",mobile__c=selected."Mobile",last_name__c=selected."LastName",billingcity=selected."BillingCity",title__c=selected."Title",billingpostalcode=selected."BillingPinCode",billingstate=selected."BillingState",billingcountry=public.getCountry(selected."BillingCountry"),billingstreet=selected."BillingAddress",customer_id__c=selected."CustomerId",status__c=selected."Status",description=selected."Description",sales_area__c=selected."SalesArea",shippingstate=selected."ShippingState",shippingstreet=selected."ShippingAddress",price_book_id__c=selected."PriceBookId",type__c=selected."Type",parent_customer_id__c=selected."ParentCustomerId",whole_seller_id__c=selected."WholeSellerId",primary_contact__c=selected."PrimaryContact",shippingpostalcode=selected."ShippingPinCode",shippingcountry=getCountry(CAST(selected."ShippingCountry" as INTEGER)),payment_term_id__c=selected."PaymentTermId",shippingcity=selected."ShippingCity"
        FROM public.tbl_customer selected WHERE selected."CustomerId" = $customerId AND toUpdate= $customerId';
        $result=DB::connection('pgsql')->update($query);
        return  $result;
    }
    //Delete Customer
    public function deleteCustomer($customerId)
    {
        $query = 'DELETE FROM salesforce.account
        WHERE customer_id__c=$customerId';
        return DB::connection('pgsql')->delete($query);
    }

     //Create OrderItem
    public function createOrderItem()
    {

        $query = 'with inserted as (
	INSERT INTO salesforce.orderitem(external_order_item_id__c,order_id__c,product_id__c,name__c,listprice,unitprice,apply_promo__c,price_book_entry__c,product_family__c,quantity,total_price__c,status__c,discount_type__c,discount__c,amount_with_gst__c,discount_in_rupees__c,sub_total__c,gst_in_rupees__c,gst_slab__c,customer_id__c,crm_id__c,order_coming_from__c,created_by_id__c,modified_date__c,updated_by__c,updated_at__c,assign_to__c)
	SELECT "OrderItemId", "OrderId", "ProductId", "Name", CAST("ListPrice" as DECIMAL(16,2)), CAST("SalePrice" as DECIMAL(16,2)), "ApplyPromo","PriceBookEntry", "ProductFamily", CAST("Quantity" as DECIMAL(18,0)), "TotalPrice", "Status", "DiscountType", "Discount", "AmountWithGST", "DiscountInRupees", "SubTotal", "GSTInRupees", "GstSlab", "CustomerId", "CrmId", "OrderComingFrom", "CreatedById","ModifiedDate", "Updated_by", "Updated_at", "AssignTo" FROM public.tbl_order_item WHERE "isSync" = false AND CAST("Quantity" as DECIMAL(18,0)) >0
	returning salesforce.orderitem.external_order_item_id__c)
	UPDATE public.tbl_order_item SET "isSync" = true FROM inserted WHERE "OrderItemId" = inserted.external_order_item_id__c';
    return DB::connection('pgsql')->unprepared($query);
    }

    //update OrderItem
    public function updateOrderItem()
    {

        $query = 'with inserted as (
	INSERT INTO salesforce.orderitem(external_order_item_id__c,order_id__c,product_id__c,name__c,list_price__c,sale_price__c,apply_promo__c,price_book_entry__c,product_family__c,quantity,total_price__c,unitprice,status__c,discount_type__c,discount__c,amount_with_gst__c,discount_in_rupees__c,sub_total__c,gst_in_rupees__c,gst_slab__c,customer_id__c,crm_id__c,order_coming_from__c,created_by_id__c,modified_date__c,updated_by__c,updated_at__c,assign_to__c)
	SELECT "OrderItemId", "OrderId", "ProductId", "Name", "ListPrice", "SalePrice", "ApplyPromo","PriceBookEntry", "ProductFamily", CAST("Quantity" as DECIMAL(18,0)), "TotalPrice", CAST("UnitPrice" as DECIMAL(16,2)), "Status", "DiscountType", "Discount", "AmountWithGST", "DiscountInRupees", "SubTotal", "GSTInRupees", "GstSlab", "CustomerId", "CrmId", "OrderComingFrom", "CreatedById", "ModifiedDate", "Updated_by", "Updated_at", "AssignTo" FROM public.tbl_order_item WHERE "isSync" = false
	returning salesforce.orderitem.external_order_item_id__c)
	UPDATE public.tbl_order_item SET "isUpdateSync" = true FROM inserted WHERE "OrderItemId" = inserted.external_order_item_id__c';
        return DB::connection('pgsql')->unprepared($query);
    }


    //Clone OrderItem
    public function cloneOrderItem()
    {
        $query = "with inserted as (
  INSERT INTO salesforce.orderitem(
	discount_in_rupees__c, discount__c, last_viewed_date__c, apply_promo__c, customer_id__c, crm_id__c, updated_by__c, discount_type__c, order_coming_from__c, updated_at__c, amount_with_gst__c, product_family__c,price_book_entry__c, status__c, gst_slab__c, sale_price__c, external_id__c, product__c, total_price__c, unit_price__c, order_id__c, created_by_id__c, assign_to__c, product_id__c, name__c, last_referenced_date__c, gst_in_rupees__c, list_price__c, sub_total__c, modified_date__c, sfid, external_order_item_id__c, quantity,unitprice,orderid,pricebookentryid,product2id)
    SELECT discount_in_rupees__c, discount__c, last_viewed_date__c, apply_promo__c, customer_id__c, crm_id__c, updated_by__c, discount_type__c, order_coming_from__c, updated_at__c, amount_with_gst__c, product_family__c,price_book_entry__c, status__c, gst_slab__c, sale_price__c, external_id__c, product__c, total_price__c, unit_price__c, order_id__c, created_by_id__c, assign_to__c, product_id__c, name__c, last_referenced_date__c, gst_in_rupees__c, list_price__c, sub_total__c, modified_date__c, sfid, external_order_item_id__c, quantity,unitprice,orderid,pricebookentryid,product2id
	FROM salesforce.orderitem WHERE sfid IS NULL AND _hc_lastop='FAILED'
  returning salesforce.orderitem.external_order_item_id__c
)
DELETE FROM salesforce.orderitem q
using inserted
WHERE q.sfid IS NULL AND q._hc_lastop ='FAILED' AND q.external_order_item_id__c = inserted.external_order_item_id__c;
";
        return DB::connection('pgsql')->unprepared($query);
    }

     public function createTable($rowTable)
    {
        // render thead
        $thead = '<thead>
        <tr>';
        foreach ($rowTable[0] as $key => $value) {
            $thead .= '<th>' . $key . '</th>';
        }
        $thead .= '</tr>
        </thead>';

        // render tbody
        $tbody = '<tbody>';
        foreach ($rowTable as $key => $value) {
            $tbody .= '<tr>';
            foreach ($value as $v) {
                $tbody .= '<td>' . $v . '</td>';
            }
            $tbody .= '</tr>';
        }
        $tbody .= '</tbody>';
        // render table
        return '<table>' . $thead . $tbody . '</table>';
    }
}