Возможные запросы к базе данных

Список всех контрагентов

select "Name", "Inn", "PartnersGrpID", "Enabled", "PhoneNumber", "ExtCode" from "dcPartners"
Наименование, инн, группа, вкл/выкл, номер телефона, внешний код.

Информация по контрагенту по ИНН

select "PartnersGrpID", "Enabled", "PhoneNumber", "ExtCode", " IsSeparateRests" from "dcPartners" where "Inn" = :"Inn"

Группа, вкл/выкл, номер телефона, внешний код, тип системы расчетов(балансовая, ограничения), балансы на доступных кошельках, кредитные ограничения на кошельках.

Информация по кошелькам

select "dcPartners"."PartnerID", "dcAmounts"."Name" as "AmountName", "dcAmounts"."AmountID" as "AmountID",
        "sysCreditTypes"."Name" as "CreditName",
        "dcCreditRestrictions"."Credit", "dcCreditRestrictions"."Period", "dcCreditRestrictions"."Price",
        "dcCreditRestrictions"."AutoMigration", "GetCreditRest"."Rest" from "dcPartners"
        left join "dcCreditRestrictions" on "dcCreditRestrictions"."PartnerID" = "dcPartners"."PartnerID"
        left join "dcAmounts" on "dcAmounts"."AmountID" = "dcCreditRestrictions"."AmountID"
        left join "sysCreditTypes" on "sysCreditTypes"."CreditTypeID" = "dcCreditRestrictions"."CreditTypeID"
        left join "GetCreditRest"("dcCreditRestrictions"."PartnerID", "dcCreditRestrictions"."AmountID") on 1=1
        where "dcPartners"."PartnerID" = :"PartnerID";

Список карт по ИНН контрагента

select "Name", "Code", "Enabled", "ExpireDate", "ExtCode", "CarNumber", "CarModel", "PhoneNumber", "Rem" from "dcCards"
where "PartnerID" = (select "PartnerID" from "dcPartners" where "Inn" = :"Inn")

Наименование карты, номер карты, вкл/выкл, использовать до, внешний код, номер ТС, модель ТС, номер телефона, отметки.

Информация по карте по номеру карты

select "Name", "Enabled", "ExpireDate", "ExtCode", "CarNumber", "CarModel", "PhoneNumber"  from "dcCards" where "Code" = : "Code"

Наименование карты, вкл/выкл, использовать до, внешний код, номер ТС, модель ТС, номер телефона, балансы на доступных кошельках, кредитные ограничения на кошельках

Кредиты на кошельках для карты

select "dcCards"."CardID", "dcAmounts"."AmountID", "dcAmounts"."Name" as "AmountName", "sysLimitTypes"."Name" as "LimitName",
            "dcLimitRestrictions"."Limit", "dcLimitRestrictions"."Period", "dcLimitRestrictions"."StartPeriod",
            "GetCreditRest_byCard"."Rest" as "Rest"
            from "dcCards"
            left join "dcLimitRestrictions" on "dcLimitRestrictions"."CardID" = "dcCards"."CardID"
            left join "dcAmounts" on "dcAmounts"."AmountID" = "dcLimitRestrictions"."AmountID"
            left join "sysLimitTypes" on "sysLimitTypes"."LimitTypeID" = "dcLimitRestrictions"."LimitTypeID"
            left join "GetCreditRest_byCard"("dcLimitRestrictions"."CardID", "dcLimitRestrictions"."AmountID") on 1=1
            where "dcCards"."CardID" = :"CardID"

Лимиты по карте

select "dcCards"."CardID", "dcAmounts"."AmountID", "dcAmounts"."Name" as "AmountName", "sysLimitTypes"."Name" as "LimitName",
        "dcLimitRestrictions"."Limit", "dcLimitRestrictions"."Period", "dcLimitRestrictions"."StartPeriod",
        case when "GetLimitRest"."Rest" < 0 then "GetCreditRest"."Rest"
         else case when "GetCreditRest"."Rest" > "GetLimitRest"."Rest" then "GetLimitRest"."Rest"
        else "GetCreditRest"."Rest" end end as "Rest"
        from "dcCards"
        left join "dcLimitRestrictions" on "dcLimitRestrictions"."CardID" = "dcCards"."CardID"
        left join "dcAmounts" on "dcAmounts"."AmountID" = "dcLimitRestrictions"."AmountID"
        left join "sysLimitTypes" on "sysLimitTypes"."LimitTypeID" = "dcLimitRestrictions"."LimitTypeID"
        left join "GetLimitRest"("dcLimitRestrictions"."CardID", "dcLimitRestrictions"."AmountID") on 1=1
        left join "GetCreditRest"("dcCards"."PartnerID", "dcLimitRestrictions"."AmountID") on 1=1
        where "dcCards"."CardID" = :"CardID";

Операции(заправки) по всем картам контрагента по ИНН

select "rgAmountRests"."Quantity",
        "rgAmountRests"."Amount",
        "rgAmountRests"."Price",
        "rgAmountRests"."Date",
        "rgAmountRests"."AZSCode",
        "dcCards"."Name"
        from "dcPartners"
        left join "rgAmountRests" on "rgAmountRests"."PartnerID" = "dcPartners"."PartnerID"
        left join "dcCards" on "rgAmountRests"."CardID" = "dcCards"."CardID"
        left join "dcAmounts" on "dcAmounts"."AmountID" = "rgAmountRests"."AmountID"
        where  "dcPartners"."PartnerID" = :"PartnerID" AND "rgAmountRests"."DocTypeID" = 3 order by "rgAmountRests"."Date" desc;

Литраж, сумма, цена, дата, азс, наименование карты

Операции(заправки) по карте по коду карты

select "rgAmountRests"."Quantity",
        "rgAmountRests"."Amount",
        "rgAmountRests"."Price",
        "rgAmountRests"."Date",
        "rgAmountRests"."AZSCode",
        "dcCards"."Name"
        from "dcCards"
        left join "rgAmountRests" on "rgAmountRests"."CardID" = "dcCards"."CardID"
        left join "dcAmounts" on "dcAmounts"."AmountID" = "rgAmountRests"."AmountID"
        where  "dcCards"."Code" = :"Code" AND "rgAmountRests"."DocTypeID" = 3 order by "rgAmountRests"."Date" desc;

Литраж, сумма, цена, дата, азс.

Запрос на изменение информации карты

update "dcCards"set "Name" = :"Name",
"Enabled" = :"Enabled",
"ExpireDate" = :"ExpireDate",
"Rem" = :"Rem",
"AllowInOffline" = :"AllowInOffline",
"PIN" = :"PIN",
"ExtCode" = :"ExtCode",
"PhoneNumber" = :"PhoneNumber",
"CarNumber" = :"CarNumber",
"CarModel" = :"CarModel",
"Email" = :"Email"
where "Code"  = :"Code"

Изменение цены топлива.

update "dcFuels" set "Price" = :price where "FuelID" = :fuleId

Только создание карты. В тригере создаются лимиты по все кошелькам

insert into "dcCards" (""PartnerID",
    "Name",
    "Code",
    "Enabled",
    "ExpireDate",
    "Rem",
    "AllowInOffline",
    "TransactionID",
    PIN,
    "ExtCode",
    "PhoneNumber",
    "CarNumber",
    "CarModel",
    "Email")
valuee (...)

Изменение лимита по карте

update "dcLimitRestrictions" set "LimitTypeID" = ?, "Limit" = ?, "Period" = ?, "StartPeriod" = ? where ("CardID" = ?) and ("AmountID" = ?)

Лимиты по карте

select "dcCards"."CardID", "dcAmounts"."AmountID", "dcAmounts"."Name" as "AmountName", "sysLimitTypes"."Name" as "LimitName",
        "dcLimitRestrictions"."Limit", "dcLimitRestrictions"."Period", "dcLimitRestrictions"."StartPeriod",
        case when "GetLimitRest"."Rest" < 0 then "GetCreditRest"."Rest"
         else case when "GetCreditRest"."Rest" > "GetLimitRest"."Rest" then "GetLimitRest"."Rest"
        else "GetCreditRest"."Rest" end end as "Rest"
        from "dcCards"
        left join "dcLimitRestrictions" on "dcLimitRestrictions"."CardID" = "dcCards"."CardID"
        left join "dcAmounts" on "dcAmounts"."AmountID" = "dcLimitRestrictions"."AmountID"
        left join "sysLimitTypes" on "sysLimitTypes"."LimitTypeID" = "dcLimitRestrictions"."LimitTypeID"
        left join "GetLimitRest"("dcLimitRestrictions"."CardID", "dcLimitRestrictions"."AmountID") on 1=1
        left join "GetCreditRest"("dcCards"."PartnerID", "dcLimitRestrictions"."AmountID") on 1=1
        where "dcCards"."CardID" = ид карты;

Создание контрагента

insert into "dcPartners" (   "Name",
    "Rem",
    "WithNDS",
    "Inn",
    "PartnersGrpID",
    "DiscountSchemaID",
    "Enabled",
    "ExtCode",
    "PhoneNumber")
values (...)

Получение кредитов по контагенту

select "dcPartners"."PartnerID", "dcAmounts"."Name" as "AmountName",
        "sysCreditTypes"."Name" as "CreditName",
        "dcCreditRestrictions"."Credit", "dcCreditRestrictions"."Period", "dcCreditRestrictions"."Price",
        "dcCreditRestrictions"."AutoMigration", "GetCreditRest"."Rest" from "dcPartners"
        left join "dcCreditRestrictions" on "dcCreditRestrictions"."PartnerID" = "dcPartners"."PartnerID"
        left join "dcAmounts" on "dcAmounts"."AmountID" = "dcCreditRestrictions"."AmountID"
        left join "sysCreditTypes" on "sysCreditTypes"."CreditTypeID" = "dcCreditRestrictions"."CreditTypeID"
        left join "GetCreditRest"("dcCreditRestrictions"."PartnerID", "dcCreditRestrictions"."AmountID") on 1=1
        where "dcPartners"."PartnerID" = ид контрагента

Дерево страниц

Возможные запросы к базе данных