- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
declare @billing_types table(k int null, t varchar(14)
collate SQL_Latin1_General_CP1255_CI_AS)
insert @billing_types
values (1, 'אשרי')
,(2, 'צ׳ק')
,(3, 'הוראת קבע')
declare @standing_order_status table(i int null, s varchar(14)
collate SQL_Latin1_General_CP1255_CI_AS)
insert @standing_order_status
values (4, 'מבותל')
,(3, 'לא מאושר')
,(2, 'ממתין')
,(1, 'מאושר')
declare @fax varchar(20)
select billing_company_id
,internal_company_name
,isnull(t, 'לא פעיל') collate SQL_Latin1_General_CP1255_CI_AS as payment_type_string
,case when company_email is null then ''
when replace((ltrim(rtrim(company_email))), '0', '') = '' then ''
-- some emails were imported in the way, they aren't valid
when charindex('@', company_email) = 0 then ''
else (ltrim(rtrim(company_email))) end as email
,case when replace(ltrim(rtrim(company_fax)), '0', '') = ''
then ''
when -- some times there are words like "none", "n/a" etc. in there
replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(ltrim(rtrim(company_fax)), '-', ''),
'0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''),
'7', ''), '8', ''), '9', '') != ''
then ''
else replace(ltrim(rtrim(company_fax)), '-', '')
end as c_fax
,ltrim(rtrim(company_address)) as c_address
,ltrim(rtrim(cast(company_comments as varchar(1000)))) as c_comments
,invoice_send_with_details
,invoice_send_fax
,invoice_print
,ltrim(rtrim(cc_name)) as c_name
,ltrim(rtrim(cc_number)) as c_number
,ltrim(rtrim(cc_cvv)) as c_cvv
,ltrim(rtrim(cc_id)) as c_id
,ltrim(rtrim(cc_expire)) as c_expire
,ltrim(rtrim(bank_number)) as number
,ltrim(rtrim(bank_branch)) as branch
,ltrim(rtrim(bank_account)) as account
,bank_hoshen
,isnull(s, 'אין') collate SQL_Latin1_General_CP1255_CI_AS as order_status
from billing_companies
left join @billing_types bt on bt.k = payment_type
left join @standing_order_status os on os.i = bank_standing_order_status
Война. Экспорт в Эксель, для того, чтобы потом ее обратно, но уже другим людям в базу передать.
Из интересных подробностей. Поле "факс" в базе имеет тип varchar(50), например. Никакой серверной валидации отродясь не было. Иногда люди просто путали факс и электорпочту, когда заполняли данные.