- 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), например. Никакой серверной валидации отродясь не было. Иногда люди просто путали факс и электорпочту, когда заполняли данные.