# coding=utf-8

jobs = {
    "cm_contract": [
        "id", "sn", "customer_id", "contract_subject", "our_customer_id",
        "org_id", "sign_org_id", "customer_contact_id", "support_id",
        "sales_id", "`type`", "sub_type", "`price`", "payed_price",
        "start_date", "expiration_date", "signed_date", "close_date",
        "child_id", "comments", "billing_period", "liquidated_damages",
        "offer_id", "offer_sn"
    ],
    "cm_legal": [
        "id", "contract_id", "pay_plan_id", "type", "start_date", "end_date",
        "status", "express_no", "signee", "comments"
    ],
    "cm_pay_plan": [
        "id", "contract_id", "create_time", "pay_date", "pay_amount",
        "receipt_status", "receipt_amount", "receipt_date", "billing_sn",
        "billing_date", "billing_sign_date", "comments", "notification_date",
        "notification_comments", "dunning_date", "dunning_comments",
        "receipt_billing_date", "billing_signed_man", "legal_date",
        "legal_comments", "litigation_date", "litigation_comments",
        "jd_comments"
    ],
    "cm_pay_plan_detail":
    ["id", "cm_pay_plan_id", "receipt_amount", "receipt_date", "comments"],
    "crm_contact": [
        "id", "customer_id", "name", "tel", "mobile", "position", "email",
        "qq", "wx", "duty_type", "is_dun", "comments", "percentage"
    ],
    "crm_customer": [
        "id", "sn", "name", "unit_type", "nature", "size", "contact_address",
        "address", "longitude", "latitude", "province", "city", "district",
        "tax_num", "bank_name", "bank_account", "bank_address", "prize_type",
        "p_vol_level", "p_capacity", "p_real_capacity", "p_our_managed",
        "comments", "add_date", "level", "company_id", "org_id", "sales_id",
        "original_sales_id", "assign_date"
    ],
    "crm_source": [
        "id", "customer_id", "sn", "meter_sn", "line_name", "price_type",
        "voltage_level", "override", "power_factor", "basic_price_type",
        "comments"
    ],
    "crm_transformer":
    ["id", "source_id", "no", "capacity", "real_capacity", "comments"],
    "crm_visit": [
        "id", "customer_id", "contact_id", "sales_id", "longitude", "latitude",
        "distance", "relationship", "report", "comments", "time"
    ],
    "duty_list": [
        "id", "start_date", "end_date", "customer_id", "personnel_id",
        "work_mode", "job"
    ],
    "et_list": [
        "id", "project_id", "experiment_type", "vol_level", "report_type",
        "content", "add_date"
    ],
    "ic_apply":
    ["id", "depart", "number", "status", "`year_month`", "comments"],
    "ic_apply_detail": ["id", "apply_id", "material_id", "number"],
    "ic_car": [
        "id", "model", "number", "start_date", "`use`", "oa_org_id",
        "oa_user_id", "insurance_due_date", "next_inspect_date", "status",
        "comments"
    ],
    "ic_car_cost": [
        "id", "ic_car_id", "oa_org_id", "oa_user_id", "`type`", "cost", "date",
        "comments"
    ],
    "ic_contract": [
        "id", "sn", "project_no", "project_name", "ic_factory_id",
        "our_customer_id", "org_id", "sales_id", "type", "sub_type", "price",
        "payed_price", "start_date", "expiration_date", "signed_date",
        "close_date", "comments"
    ],
    "ic_device": [
        "id", "customer_id", "source_id", "material_id", "device_belong",
        "category", "cabinet_no", "number", "made_date", "comments"
    ],
    "ic_factory": [
        "id", "level", "name", "contact", "tel", "address", "comments",
        "is_delete"
    ],
    "ic_instock": [
        "id", "no", "type", "buyer", "project_id", "storehouse", "agent_id",
        "in_date", "status", "comments"
    ],
    "ic_instock_detail": [
        "id", "instock_id", "material_id", "storage", "number", "price",
        "amount"
    ],
    "ic_material": [
        "id", "type_id", "factory_id", "name", "model", "unit",
        "in_refer_price", "out_refer_price", "comments", "is_delete"
    ],
    "ic_material_test": [
        "id", "type_id", "factory_id", "name", "model", "unit",
        "in_refer_price", "out_refer_price", "comments"
    ],
    "ic_material_type": [
        "id", "category", "name", "profit_rate", "unmanaged_rate",
        "managed_rate", "branch_rate", "comments", "is_delete"
    ],
    "ic_offer": [
        "id", "oa_org_id", "oa_user_id", "our_address", "our_phone",
        "our_email", "review_date", "nuclear_price_date", "crm_customer_id",
        "customer_address", "customer_contact", "customer_phone",
        "customer_email", "managed", "project_type", "no", "construction_date",
        "apply_date", "approve_date", "status", "reviewer_id",
        "nuclear_price_id", "approver_id", "comment", "sales_comment",
        "review_comment", "marketing_comment", "approve_comment", "price"
    ],
    "ic_offer_detail": [
        "id", "no", "ic_material_id", "ic_offer_id", "cost_price",
        "sell_price", "number", "comment"
    ],
    "ic_outstock": [
        "id", "no", "type", "storehouse", "project_id", "users", "agent_id",
        "out_date", "status", "comments"
    ],
    "ic_outstock_detail": [
        "id", "outstock_id", "material_id", "storage", "number", "in_price",
        "out_price", "amount"
    ],
    "ic_report": [
        "id", "material_id", "`year_month`", "last_number", "in_number",
        "out_number", "stock"
    ],
    "ic_stock":
    ["id", "material_id", "location", "storage", "stock", "real_price"],
    "im_alert_list": [
        "id", "device_id", "type", "level", "value", "status", "start_time",
        "last_time", "end_time", "times"
    ],
    "im_alert_value": [
        "id", "device_id", "uhigh_red", "uhigh_yellow", "uhigh_send",
        "ulow_red", "ulow_yellow", "ulow_send", "i_red", "i_yellow", "i_send",
        "pf_red", "pf_send", "md_red", "md_send", "ep_red", "ep_yellow",
        "ep_send", "t_send", "s_send", "outage_send", "offline_send", "sendto"
    ],
    "im_customer": [
        "id", "customer_id", "user_id", "agent_id", "server_no", "start_date",
        "end_date", "bill_date"
    ],
    "im_device": [
        "id", "im_customer_id", "sn", "name", "type", "status", "online_time",
        "sim_no", "sim_type", "frequency", "source_id", "transformer_id", "no",
        "v_c", "i_c", "x", "y", "non_summer_price", "summer_price",
        "voltage_line"
    ],
    "im_load": ["id", "im_device_id", "max_ep", "max_load_rate", "date"],
    "im_power": [
        "id", "im_device_id", "date", "power_efficiency", "power_generation",
        "weather"
    ],
    "im_real": [
        "id", "imei", "line", "ua", "ub", "uc", "ia", "ib", "ic", "pa", "pb",
        "pc", "psum", "qa", "qb", "qc", "qsum", "pfa", "pfb", "pfc", "pfsum",
        "fr", "px", "eq1", "eq2", "ep", "tc", "t", "s", "ht", "ti", "net",
        "tm", "comments"
    ],
    "io_billing": [
        "id", "source_id", "`year_month`", "peak_volume", "peak_volume2",
        "tip_volume", "tip_volume2", "usual_volume", "usual_volume2",
        "valley_volume", "valley_volume2", "other_volume", "peak_price",
        "peak_price2", "tip_price", "tip_price2", "usual_price",
        "usual_price2", "valley_price", "valley_price2", "other_price",
        "levy_fee", "power_factor", "power_factor_real", "power_factor_adjust",
        "report_md", "max_md", "basic_price", "capacity", "idle_volume",
        "amount", "amount_adjust"
    ],
    "io_customer": [
        "id", "customer_id", "contact_id", "contract_id", "inspector_id",
        "start_date", "end_date", "last_et_date", "et_date", "et_charge",
        "customize_date", "times", "md_report", "check_date1", "check_date2",
        "check_date3", "check_date4", "check_date5", "check_date6", "comments",
        "stopped", "config_num", "shift_time"
    ],
    "io_defect": [
        "id", "io_customer_id", "input_date", "close_date", "level",
        "position", "`desc`", "result", "advise", "business", "status",
        "contract_id", "return_amount", "img_src"
    ],
    "io_inspect": [
        "id", "customer_id", "`year_month`", "date", "temp", "humi", "weather",
        "house1", "house2", "house3", "house4", "house5", "house6",
        "facility1", "facility2", "facility3", "facility4", "facility5",
        "facility6", "inspector", "status", "comments"
    ],
    "io_inspect_in": [
        "id", "inspect_id", "source_id", "active_volume", "peak_volume",
        "usual1_volume", "usual2_volume", "valley_volume", "idle1_volume",
        "idle2_volume", "power_factor_real", "peak_md", "usual1_md",
        "usual2_md", "valley_md", "max_md", "v_ab", "v_bc", "v_ca", "pd_a",
        "pd_b", "pd_c"
    ],
    "io_inspect_out": [
        "id", "inspect_id", "source_id", "transformer_id", "o_ia", "o_ib",
        "o_ic", "o_pda", "o_pdb", "o_pdc", "t_voice", "t_fan", "t_oil",
        "t_dry", "t_exception", "t_temp", "s_vab", "s_vbc", "s_vca", "s_ia",
        "s_ib", "s_ic", "ec_status"
    ],
    "io_md": [
        "id", "source_id", "`year_month`", "capacity", "capacity_real",
        "report_type", "report_md", "max_md", "inspect_md", "last_report_md",
        "date", "status", "comments", "reason"
    ],
    "io_price": [
        "id", "oa_org_id", "name", "tip_price", "peak_price", "usual_price",
        "valley_price", "other_price", "basic_price", "voltage_level",
        "price_type", "is_summer"
    ],
    "mm_target": ["id", "company_id", "org_id", "sales_id", "year", "target"],
    "oa_log": [
        "id", "user_id", "action", "info", "time", "module", "page", "data_id",
        "original_data", "modified_data", "save_data"
    ],
    "oa_org": [
        "id", "name", "level", "superior", "type", "distinguish_summer",
        "electricity_type", "summer_time"
    ],
    "oa_right": [
        "id", "oa_role_id", "module", "page", "data_range", "visit", "`add`",
        "`delete`", "`check`", "review", "edit"
    ],
    "oa_role": ["id", "name", "type"],
    "oa_user": [
        "id", "name", "password", "nick", "role", "role_id", "contact_id",
        "weixin_id", "company_id", "org_id", "target", "login_time", "login_ip"
    ],
    "personnel_list": [
        "id", "hire_date", "departure_date", "name", "born_date", "sex",
        "mobile", "status", "part_time_company", "willing", "address",
        "longitude", "latitude", "comment", "add_date"
    ],
    "pm_follow": ["id", "pm_item_id", "date", "content"],
    "pm_implement": [
        "id", "pm_project_id", "content", "start_time", "end_time",
        "customer_on_site", "salesman_on_site", "start_longitude",
        "start_latitude", "end_longitude", "end_latitude", "participants_id",
        "wage", "comments"
    ],
    "pm_item": [
        "id", "crm_customer_id", "cm_contract_id", "project_mange",
        "process_analysis", "progress1_date", "progress2_date",
        "progress3_date", "progress4_date", "progress5_date", "progress6_date",
        "progress7_date", "progress8_date", "progress9_date",
        "real_progress1_date", "real_progress2_date", "real_progress3_date",
        "real_progress4_date", "real_progress5_date", "real_progress6_date",
        "real_progress7_date", "real_progress8_date", "real_progress9_date",
        "bidding", "win_bidding_date", "start_date", "suspend_date",
        "termination_date", "end_date", "status", "comments", "archives_no",
        "keep_archives", "archives_comments"
    ],
    "pm_project": [
        "id", "project_sn", "crm_customer_id", "application_time",
        "crm_contact_id", "content", "end_time", "start_time", "information",
        "cm_contract_id", "type", "status", "send_workers_id", "dispatch_time",
        "implement_start_time", "implement_end_time", "project_message",
        "construction_id", "estimated_man_hour", "accept_time", "tell_date",
        "power_failure", "work_ticket", "application_id", "audit_time",
        "return_time"
    ],
    "pm_quality": [
        "id", "project_id", "customer_service_id", "quality", "attitude",
        "appearance", "opinion"
    ],
    "pm_support": [
        "id", "sales_id", "customer_name", "customer_address", "contact_name",
        "contact_mobile", "type", "descriptions", "suppose_date",
        "dispatch_org_id", "supporter_id", "process", "apply_time",
        "response_time", "support_time", "satisfaction", "comment", "status",
        "reject_reason", "comment_time", "sign_contract_id", "close_reason",
        "close_time", "estimated_amt", "comments"
    ],
    "pm_support_follow": ["id", "pm_support_id", "content", "follow_time"],
    "points_assign": [
        "id", "crm_contact_id", "points_user_id", "cm_contract_id",
        "percentage", "points_ratio", "status", "comments"
    ],
    "points_goods":
    ["id", "name", "img_src", "`desc`", "points", "stock", "status"],
    "points_order": [
        "id", "goods_id", "crm_contact_id", "points_user_id", "address",
        "points", "num", "express_company", "express_num", "status",
        "add_time", "approve_time", "deliver_time", "cancel_comment",
        "reject_comment"
    ],
    "points_receipt": [
        "id", "name", "crm_contact_id", "points_user_id", "mobile", "district",
        "address", "`default`"
    ],
    "points_record": [
        "id", "crm_contact_id", "points_user_id", "type", "points",
        "points_usable", "add_time"
    ],
    "points_total": [
        "id", "crm_contact_id", "points_user_id", "token", "points",
        "points_expired", "points_used", "points_usable", "continuous_days",
        "login_time"
    ],
    "points_user": [
        "id", "nick", "mobile", "name", "password", "token", "url", "status",
        "comments"
    ],
    "rm_list": [
        "id", "pay_id", "sales_id", "role", "`year_month`", "status",
        "receipt_amount", "is_renew", "bussiness_cost", "transformer_count",
        "percentage", "role_percentage", "actual_price", "paid_price",
        "apply_date", "check_date", "approve_date", "comment", "check_comment",
        "review_comment", "approve_comment"
    ],

}


trans_jobs = {
    "customer": {
        "query_sql": ["SELECT 5000 + A.id as id, CONCAT('OA', A.id,'-', A.sn) as `code`, A.`name` as `name`, A.`level` as `level`, CASE WHEN B.id is not null and C.id is not null then '1,10' WHEN B.id is not null and C.id is null then '1' WHEN B.id is null and C.id is null then '0' WHEN B.id is null and C.id is not null then '10' END as `type`, 5000 + A.id as `enterprise_id`, if(A.original_sales_id<=0,null, A.original_sales_id) as `last_service_staff_id`, if(A.sales_id<=0,null,A.sales_id) as `service_staff_id`, case when A.id in (2994, 3026, 28867, 30538, 30598, 34235, 61073) then 1002 when A.id in (5100, 5473, 5987, 75852, 75853, 75854) then 1004 when A.org_id in (29,30,36,20,54) then 54 when A.org_id in (75,76,80,52) then 52 when A.org_id in(28,70) then 70 when A.org_id in(19,31,32,33,34,35,37,43,44,45,46,47,48,53) then 19 when A.org_id in(41,39,40,42,50) then 41 when A.org_id in(25,78) then 78 when A.org_id=6 then 6 when A.org_id=49 then 49 when A.org_id=23 then 23 else 1 end as service_enterprise_id,'2020-10-26 12:13:03.850262' as created_time, '2020-10-26 12:13:03.850262' as updated_time FROM crm_customer A LEFT JOIN io_customer B ON A.id=B.customer_id LEFT JOIN im_customer C ON A.id=C.customer_id WHERE A.`level` <= 120"],
        "column": [
            "id",
            "code",
            "name",
            "`level`",
            "`type`",
            "enterprise_id",
            "last_service_staff_id",
            "service_staff_id",
            "service_enterprise_id",
            "created_time",
            "updated_time"
        ]
    },
    # "accounts": {
    #     "query_sql": ["SELECT 5000 + id as id, CONCAT('EPK', id,'-', sn) as `username`, '' as `first_name`, '' as `last_name`, '' as `email`, 'customer' as `account_type`, '2020-10-26 12:13:03.703070' as `date_joined`, '2020-10-26 12:13:03.850262' as `update_time`, '2020-10-26 12:13:03.850262' as `create_time` FROM crm_customer WHERE `level` <= 120 union SELECT `id` as `id`, `name` as `username`, '' as `first_name`, '' as `last_name`, '' as `email`,'manager' as `account_type`, '2020-10-26 12:13:03.703070' as `date_joined`, '2020-10-26 12:13:03.850262' as `update_time`, '2020-10-26 12:13:03.850262' as `create_time` FROM oa_user"],
    #     "column": [
    #         "id",
    #         "`username`",
    #         "`first_name`",
    #         "`last_name`",
    #         "`email`",
    #         "`account_type`",
    #         "`date_joined`",
    #         "`update_time`",
    #         "`create_time`",
    #     ]
    # },
    # "users": {
    #     "query_sql": ["SELECT 5000 + id as id, `name` as `nickname`, '' as `wechat`, '2020-10-26 12:13:03.850262' as `created_time`, '2020-10-26 12:13:03.850262' as `updated_time`, '' as `phone`, '' as `email`, 5000 + id as account_id FROM crm_customer WHERE `level` <= 120 union SELECT `id` as `id`, `nick` as `nickname`, `weixin_id` as `wechat`, '2020-10-26 12:13:03.850262' as `created_time`, '2020-10-26 12:13:03.850262' as `updated_time`, '' as `phone`, '' as `email`, id as account_id FROM oa_user"],
    #     "column": [
    #         "id",
    #         "`nickname`",
    #         "`wechat`",
    #         "`created_time`",
    #         "`updated_time`",
    #         "`phone`",
    #         "`email`",
    #         "`account_id`",
    #     ]
    # },
    # "customer_admin": {
    #     "query_sql": ["SELECT 5000 + `id` as `id`, 5000 + `id` as `user_id`, 5000 + `id` as `customer_id`, '2020-10-26 12:13:03.850262' as `created_time`, '2020-10-26 12:13:03.850262' as `updated_time` FROM crm_customer WHERE `level` <= 120"],
    #     "column": [
    #         "`id`",
    #         "`user_id`",
    #         "`customer_id`",
    #         "`created_time`",
    #         "`updated_time`",
    #     ]
    # },
    "enterprise": {
        # "query_sql": ["SELECT 5000 + id as 'id', CONCAT('ENT', id,'-', sn) as `enterprise_id`, `name` as `name`, `longitude` as `longitude`, `latitude` as `latitude`, `address` as `address`, `province` as 'adcode', `city` as `city_code`, `district`,`level` as `level`, `unit_type` as `unit_type`, `tax_num` as `tax_num`, `bank_account` as `bank_account_name`, `bank_name`, `size` as `scale`, `nature` as `nature` FROM crm_customer WHERE `level` <= 120 union SELECT id as 'id', CONCAT('EPK', id) as `enterprise_id`, `name` as `name`, null as `longitude`, null as `latitude`, null as `address`, null as 'adcode', null as 'city_code', null as `district`, (5-`level`)*5+120 as `level`, 0 as `unit_type`, null as `tax_num`, null as `bank_account_name`, null as `bank_name`, null as `scale`, null as `nature` FROM oa_org"],
        "query_sql": ["SELECT 5000 + id as 'id', CONCAT('ENT', id,'-', sn) as `enterprise_id`, `name` as `name`, `longitude` as `longitude`, `latitude` as `latitude`, `address` as `address`, `province` as 'adcode', `city` as `city_code`, `district`,`level` as `level`, `unit_type` as `unit_type`, `tax_num` as `tax_num`, `bank_account` as `bank_account_name`, `bank_name`, `size` as `scale`, `nature` as `nature` FROM crm_customer WHERE `level` <= 120"],
        "column": [
            "`id`",
            "`enterprise_id`",
            "`name`",
            "`longitude`",
            "`latitude`",
            "`address`",
            "`adcode`",
            "`city_code`",
            "`district`",
            "`level`",
            "`unit_type`",
            "`tax_num`",
            "`bank_account_name`",
            "`bank_name`",
            "`scale`",
            "`nature`",
            # "`admin_id`",
        ]
    },
    # "user_enterprise": {
    #     "query_sql": ["SELECT 5000 + id as id, 5000 + id as user_id, CONCAT('ENT', id,'-', sn) as `enterprise_id` FROM crm_customer WHERE `level` <= 120 union SELECT `id` as `id`, `id` as user_id, CONCAT('EPK', if(org_id not in (6, 52),1,org_id)) as tel FROM oa_user"],
    #     "column": [
    #         "id",
    #         "user_id",
    #         "enterprise_id",
    #     ],
    # },
    "contract": {
        "query_sql": ["select id, child_id as 'child_id', sn as 'code', `price` as 'amount', payed_price, if(signed_date='0000-00-00', null, signed_date) as 'entry_date', if(start_date='0000-00-00', null, start_date) as 'effective_date', if(expiration_date='0000-00-00', null, expiration_date) as 'end_date', if(close_date='0000-00-00', null, close_date) as 'closed_date', comments as 'remark', customer_id + 5000 as 'customer_id', customer_id + 5000 as 'business_entity_id', case when customer_id in (2994, 3026, 28867, 30538, 30598, 34235, 61073) then 1002 when customer_id in (5100, 5473, 5987, 75852, 75853, 75854) then 1004 when org_id in (29,30,36,20,54) then 54 when org_id in (75,76,80,52) then 52 when org_id in(28,70) then 70 when org_id in(19,31,32,33,34,35,37,43,44,45,46,47,48,53) then 19 when org_id in(41,39,40,42,50) then 41 when org_id in(25,78) then 78 when org_id=6 then 6 when org_id=49 then 49 when org_id=23 then 23 else 1 end as 'contract_party_id', org_id as 'organization_id', if(customer_contact_id<=0, null,customer_contact_id) as 'customer_contact_id', if(sales_id<=0,null,sales_id) as 'salesman_id', `type`, support_id, sub_type, billing_period, liquidated_damages, offer_id, offer_sn from cm_contract"],
        "column": [
            "id",
            "child_id",
            "code",
            "amount",
            "payed_price",
            "entry_date",
            "effective_date",
            "end_date",
            "closed_date",
            "remark",
            "customer_id",
            "business_entity_id",
            "contract_party_id",
            "organization_id",
            "customer_contact_id",
            "salesman_id",
            "`type`",
            "support_id",
            "sub_type",
            "billing_period",
            "liquidated_damages",
            "offer_id",
            "offer_sn"
        ],
    },
    "electricity_user":{
        "query_sql": ["SELECT 5000 + A.id as id, '2020-10-26 12:13:03.850262' as `created_time`, '2020-10-26 12:13:03.850262' as `updated_time`, A.sn as `number`, A.`address` as addr, A.p_capacity as transformer_capacity, A.p_real_capacity as real_capacity, A.p_vol_level as voltage_level, B.power_factor as ep_factor, A.prize_type as `type`, A.p_our_managed as trusteeship_num, A.`longitude` as `longitude`, A.`latitude` as `latitude`, A.`district` as `ad_code`, A.`city` as `city_code`, A.`name` as householder, 5000 + A.id as customer_id, B.basic_price_type as billing_method, C.contact_id report_contact_id FROM crm_customer as A left join crm_source B on B.customer_id = A.id left join io_customer C on A.id=C.customer_id WHERE `level` <= 120 and A.id != 23570 group by A.id"],
        "column": [
            "`id`",
            "`created_time`",
            "`updated_time`",
            "`number`",
            "`addr`",
            "`transformer_capacity`",
            "`real_capacity`",
            "`voltage_level`",
            "`ep_factor`",
            "`type`",
            "`trusteeship_num`",
            "`longitude`",
            "`latitude`",
            "`ad_code`",
            "`city_code`",
            "`householder`",
            "`customer_id`",
            "`billing_method`",
            "`report_contact_id`",
        ]
    },
    "power_station": {
        # "query_sql": ["select 5000 + A.id as id, '2020-10-26 12:13:03.850262' as `created_time`, '2020-10-26 12:13:03.850262' as `updated_time`, if(B.line_name is null or B.line_name='',A.name,B.line_name) as `name`, A.`address` as addr, A.city as city_code, A.district as ad_code, D.times as inspections_number, SUM(C.capacity) as total_capacity, SUM(C.real_capacity) as real_capacity, 5000 + A.id as customer_id, 5000 + A.id as electricity_user_id, D.start_date as start_time, D.end_date as end_time, if(D.et_date='0000-00-00',null,D.et_date) as electric_test_time, if(D.customize_date='0000-00-00',null,D.customize_date) as customize_time,case when D.inspector_id=2674 then 2632 when D.inspector_id=0 then null when D.inspector_id in (1969,2631) then 90017 else D.inspector_id end as inspector_ids,case when D.inspector_id=2674 then 2632 when D.inspector_id=0 then null when D.inspector_id in (1969,2631) then 90017 else D.inspector_id end as service_team,ifnull(D.stopped,0) as deleted FROM crm_customer as A right join crm_source B on B.customer_id = A.id left join (select sum(capacity) as capacity, sum(real_capacity) as real_capacity, source_id from crm_transformer group by source_id) C on C.source_id = B.id left join io_customer D on D.customer_id = B.customer_id WHERE A.`level` <= 120 GROUP BY A.id"],
        "query_sql": ["select 5000 + A.id as id, '2020-10-26 12:13:03.850262' as `created_time`, '2020-10-26 12:13:03.850262' as `updated_time`, if(B.line_name is null or B.line_name='',A.name,B.line_name) as `name`, A.`address` as addr, A.city as city_code, A.district as ad_code, D.times as inspections_number, SUM(C.capacity) as total_capacity, SUM(C.real_capacity) as real_capacity, 5000 + A.id as customer_id, 5000 + A.id as electricity_user_id, D.start_date as start_time, D.end_date as end_time, if(D.et_date='0000-00-00',null,D.et_date) as electric_test_time, if(D.customize_date='0000-00-00',null,D.customize_date) as customize_time, case when A.id in (2994, 3026, 28867, 30538, 30598, 34235, 61073) then 1002 when A.id in (5100, 5473, 5987, 75852, 75853, 75854) then 1004 when A.org_id=6 then 6 when A.org_id=49 then 49 when A.org_id=23 then 23 when A.org_id in (76, 52) then 52 when A.org_id in (29,30,20,54) then 54 when A.org_id in(28,70) then 70 when A.org_id in(19,31,32,33,34,35,37,43,44,45,46,47,48,53) then 19 when A.org_id in(41,39,40,42,50) then 41 when A.org_id in(25,78) then 78 else 1 end service_enterprise_id, ifnull(D.stopped,0) is_stop FROM crm_customer as A right join crm_source B on B.customer_id = A.id left join (select sum(capacity) as capacity, sum(real_capacity) as real_capacity, source_id from crm_transformer group by source_id) C on C.source_id = B.id left join io_customer D on D.customer_id = B.customer_id WHERE A.`level` <= 120 GROUP BY A.id"],
        "column": [
            "`id`",
            "`created_time`",
            "`updated_time`",
            "`name`",
            "`addr`",
            "`city_code`",
            "`ad_code`",
            "`inspections_number`",
            "`total_capacity`",
            "`real_capacity`",
            "`customer_id`",
            "`electricity_user_id`",
            "`start_time`",
            "`end_time`",
            "`electric_test_time`",
            "`customize_time`",
            "`service_enterprise_id`",
            # "`inspector_ids`",
            # "`service_team`",
            "`is_stop`",
            # "`deleted`",
        ]
    },
    "electrical_info": {
        "query_sql": ["select A.id, '2020-10-26 12:13:03.850262' as `created_time`,  '2020-10-26 12:13:03.850262' as `updated_time`, A.sn as power_number, A.meter_sn as meter_number, A.override as magnification, A.line_name as incoming_line_name, A.comments as `comment`, 5000 + A.customer_id as powerstation_id, B.capacity as transformer_capacity, B.real_capacity as real_capacity, 0 as outline_number, 5000 + A.customer_id as customer_id, 5000 + A.customer_id as electricity_user_id,if(A.voltage_level=0 or A.voltage_level is null,C.`p_vol_level`,A.voltage_level) voltage_level,A.power_factor ep_factor, A.price_type tariff_type from crm_source A left join (select sum(capacity) as capacity, sum(real_capacity) as real_capacity, source_id from crm_transformer group by source_id) B on A.id = B.source_id left join crm_customer C on C.id=A.customer_id"],
        "column": [
            "`id`",
            "`created_time`",
            "`updated_time`",
            "`power_number`",
            "`meter_number`",
            "`magnification`",
            "`incoming_line_name`",
            "`comment`",
            "`powerstation_id`",
            "`transformer_capacity`",
            "`real_capacity`",
            "`outline_number`",
            "`customer_id`",
            "`electricity_user_id`",
            "`voltage_level`",
            "`ep_factor`",
            "`tariff_type`",
        ]
    },
    # "team":{
    #     "query_sql":["select id, concat(nick, '组') as `name`, id as team_headman_id, case when org_id=6 then 6 when org_id in (76, 52) then 52 when org_id in (29,30,20,54) then 54 else 1 end enterprise_id from oa_user where id in (select distinct inspector_id from io_customer)"],
    #     "column": [
    #         "`id`",
    #         "`name`",
    #         "`team_headman_id`",
    #         "`enterprise_id`"
    #     ]
    # },
    "contact":{
        "query_sql":["select id, `name`, customer_id+5000 customer_id, tel, mobile as phone, email, qq, wx as wechat, comments, '2021-01-13 11:18:38' created_time,'2021-01-13 11:18:38' updated_time from crm_contact"],
        "column": [
            "`id`",
            "`name`",
            "`customer_id`",
            "`tel`",
            "`phone`",
            "`email`",
            "`qq`",
            "`wechat`",
            "`comments`",
            "`created_time`",
            "`updated_time`",
        ]
    },
    # "oa_real_data":{
    #     "query_sql":["select CAST(sum(ifnull(C.capacity,0)) AS SIGNED) capacity, sum(ifnull(A.psum,0)) power, sum(ifnull(A.ep,0)) ep, sum(ifnull(A.eq1,0))+sum(ifnull(A.eq2,0)) eq, E.org_id enterprise_id, E.province, E.city, now() tm from  im_real A left join im_device B on A.imei=B.sn left join crm_transformer C on B.transformer_id = C.id left join crm_source D on C.source_id=D.id left join crm_customer E on D.customer_id=E.id where A.tm>=date_format(NOW(), '%Y-%m-%d') and C.capacity is not null and E.province is not null and E.city is not null and E.province!=0 and E.city!=0 group by E.org_id,E.province,E.city"],
    #     "column": [
    #         "`capacity`",
    #         "`power`",
    #         "`ep`",
    #         "`eq`",
    #         "`enterprise_id`",
    #         "`province`",
    #         "`city`",
    #         "`tm`"
    #     ]
    # },
}

# oa监控设备同步
# select
# id,
# 0 deleted,
# if(online_time='0000-00-00 00:00:00',null,online_time) created_time,
# sn imei,
# case when `type` in (4,5) then 3
# when `type` in (6,7) then 4
# when `type` in (2,3) then 2
# else `type` end manufacturer,
# case when `type`=2 then 4
# when `type`=3 then 5
# when `type`=4 then 6
# when `type`=5 then 7
# when `type`=3 then 5 else `type` end model,
# 2 network_type,
# sim_no sim,
# `status`,
# id `monitor_point_id`,
# 1 enterprise_id,
# 1 from_oa
# from im_device where id>2269

# oa监控点同步
# select
# A.id,
# 0 deleted,
# if(A.online_time='0000-00-00 00:00:00',null,A.online_time) created_time,
# if(A.online_time='0000-00-00 00:00:00',null,A.online_time) updated_time,
# A.`name`,
# B.customer_id,
# B.customer_id electricity_user_id,
# B.customer_id station_id,
# 1 template_id,
# 0 `status`,
# 300 frequency,
# 1 from_oa,
# D.capacity power,
# concat("tb_", C.sn) oa_table,
# D.no oa_line
# from im_device A
# left join crm_source B
# ON A.source_id=B.id
# left join crm_customer C
# on C.id=B.customer_id
# left join crm_transformer D
# on A.transformer_id=D.id


# 设备同步
# select a.id, concat("变压器-",b.meter_sn,"-",a.no) `name`, b.customer_id + 5000 customer_id, b.customer_id + 5000 electricity_user_id, b.id electrical_info_id, 0 pid, a.id equipment_data_id from crm_transformer a left join crm_source b on a.source_id=b.id

# 设备详情同步
# select a.id, 1 modular, 1201 `type`, "NA" manufacturer, "NA" model, a.capacity, a.real_capacity, 1 `status`, a.comments from crm_transformer a 