$PBExportHeader$uo_import_mtrlname.sru
forward
global type uo_import_mtrlname from nonvisualobject
end type
end forward

global type uo_import_mtrlname from nonvisualobject
end type
global uo_import_mtrlname uo_import_mtrlname

forward prototypes
private function string pf_getid (string arg_ctg, datetime arg_dt, boolean if_commit, transaction arg_transaction)
public function integer uf_reset (ref s_import_mtrlname arg_s_import, ref string arg_msg)
public function integer uf_del (long arg_im_type, string arg_im_id, ref string arg_msg, boolean arg_if_commit)
public function integer uf_getinfo (long arg_im_type, string arg_im_id, ref s_import_mtrlname arg_s_import, ref string arg_msg)
public function integer uf_getinfo_dscrp (long arg_im_type, string arg_im_id, ref string arg_dscrp, ref string arg_msg)
public function integer uf_save_dscrp (s_import_mtrlname arg_s_import, ref string arg_msg, boolean arg_if_commit)
public function integer uf_xls_to_import_mtrlcode (string arg_filepath, ref string arg_msg, boolean arg_ifcommit, boolean arg_ignore)
public function integer uf_savemx (long arg_im_type, string arg_im_id, s_import_mtrlnamemx arg_mx[], ref string arg_msg)
public function integer uf_csv_to_import_mtrlcode (string arg_filepath, ref s_import_mtrlname arg_import[], ref string arg_msg)
public function integer uf_save_from_web (s_import_mtrlname arg_s_import, ref string arg_msg, boolean arg_if_commit)
public function integer uf_save (s_import_mtrlname arg_s_import, ref string arg_msg, boolean arg_if_commit)
end prototypes

private function string pf_getid (string arg_ctg, datetime arg_dt, boolean if_commit, transaction arg_transaction);String rslt = ''
Long ll_nmbr

arg_ctg = Upper(arg_ctg)


arg_dt = DateTime(Date(arg_dt), Time(0))

UPDATE  u_bsnno_aps
	SET nmbr = nmbr + 1
	WHERE opDate = :arg_dt
	AND typecode = :arg_ctg
	AND scid = 0
	And status = 0 Using arg_transaction;
IF arg_transaction.SQLCode <> 0 THEN
	ROLLBACK Using arg_transaction ;
	rslt = "err"
	GOTO ext
END IF

IF arg_transaction.SQLNRows = 0 THEN
	INSERT INTO u_bsnno_aps(scid, opdate, typecode, nmbr, status)
		Values (0, :arg_dt, :arg_ctg, 1, 0) Using arg_transaction;
		
	IF arg_transaction.SQLCode <> 0 THEN
		ROLLBACK Using arg_transaction ;
		rslt = "err"
		GOTO ext
	END IF
END IF

SELECT nmbr
	INTO :ll_nmbr
	FROM  u_bsnno_aps
	WHERE opDate = :arg_dt
	AND typecode = :arg_ctg
	AND scid = 0
	And status = 0 Using arg_transaction;
IF arg_transaction.SQLCode <> 0 THEN
	ROLLBACK Using arg_transaction ;
	rslt = "err"
	GOTO ext
END IF

rslt = arg_ctg + String(arg_dt, 'yyMMdd') + String(ll_nmbr, '000')

IF rslt = '' THEN rslt = 'err'

IF if_commit And rslt <> "err" THEN
	COMMIT Using arg_transaction;
END IF

ext:
RETURN rslt








end function

public function integer uf_reset (ref s_import_mtrlname arg_s_import, ref string arg_msg);Int rslt = 1

IF IsNull(arg_s_import.im_type) THEN arg_s_import.im_type = 0
IF IsNull(arg_s_import.im_id) THEN arg_s_import.im_id = ''
IF IsNull(arg_s_import.mtrlid) THEN arg_s_import.mtrlid = 0
IF IsNull(arg_s_import.eb_num) THEN arg_s_import.eb_num = 0
IF IsNull(arg_s_import.eb_price) THEN arg_s_import.eb_price = 0
IF IsNull(arg_s_import.eb_type) THEN arg_s_import.eb_type = ''
IF IsNull(arg_s_import.eb_stuffStatus) THEN arg_s_import.eb_stuffStatus = ''
IF IsNull(arg_s_import.eb_title) THEN arg_s_import.eb_title = ''
IF IsNull(arg_s_import.eb_dscrp) THEN arg_s_import.eb_dscrp = ''
IF IsNull(arg_s_import.eb_locationstate) THEN arg_s_import.eb_locationstate = ''
IF IsNull(arg_s_import.eb_locationcity) THEN arg_s_import.eb_locationcity = ''
IF IsNull(arg_s_import.eb_cid) THEN arg_s_import.eb_cid = ''
IF IsNull(arg_s_import.numIid) THEN arg_s_import.numIid = ''
IF IsNull(arg_s_import.props) THEN arg_s_import.props = ''
IF IsNull(arg_s_import.im_userid) THEN arg_s_import.im_userid = ''


RETURN rslt

end function

public function integer uf_del (long arg_im_type, string arg_im_id, ref string arg_msg, boolean arg_if_commit);Int rslt = 1

DELETE FROM eb_import_mtrlname
	Where im_id = :arg_im_id And im_type = :arg_im_type;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '删除失败,' + sqlca.SQLErrText
	GOTO ext
END IF

ext:
IF arg_if_commit THEN
	IF rslt = 1 THEN
		COMMIT;
	ELSE
		ROLLBACK;
	END IF
END IF

RETURN rslt


end function

public function integer uf_getinfo (long arg_im_type, string arg_im_id, ref s_import_mtrlname arg_s_import, ref string arg_msg);Int rslt = 1
s_import_mtrlname s_empty
arg_s_import = s_empty
SELECT im_type,
	im_id,
	mtrlid,
	eb_num,
	eb_price,
	eb_type,
	eb_stuffStatus,
	eb_title,
	eb_dscrp,
	eb_locationstate,
	eb_locationcity,
	eb_cid,
	numIid,
	props,
	im_userid
	INTO
	:arg_s_import.im_type,
	:arg_s_import.im_id,
	:arg_s_import.mtrlid,
	:arg_s_import.eb_num,
	:arg_s_import.eb_price,
	:arg_s_import.eb_type,
	:arg_s_import.eb_stuffStatus,
	:arg_s_import.eb_title,
	:arg_s_import.eb_dscrp,
	:arg_s_import.eb_locationstate,
	:arg_s_import.eb_locationcity,
	:arg_s_import.eb_cid,
	:arg_s_import.numIid,
	:arg_s_import.props,
	:arg_s_import.im_userid
	FROM eb_import_mtrlname
	WHERE im_type = :arg_im_type
	And im_id = :arg_im_id;
	
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询商品信息失败,' + sqlca.SQLErrText
	GOTO ext
END IF

ext:
RETURN rslt



end function

public function integer uf_getinfo_dscrp (long arg_im_type, string arg_im_id, ref string arg_dscrp, ref string arg_msg);Int rslt = 1

SELECT eb_dscrp
	INTO :arg_dscrp
	FROM eb_import_mtrlname
	WHERE im_type = :arg_im_type
	And im_id = :arg_im_id;
	
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询商品信息失败,' + sqlca.SQLErrText
	GOTO ext
END IF

ext:
RETURN rslt


end function

public function integer uf_save_dscrp (s_import_mtrlname arg_s_import, ref string arg_msg, boolean arg_if_commit);Int rslt = 1

UPDATE eb_import_mtrlname
	SET eb_dscrp = :arg_s_import.eb_dscrp
	WHERE im_type = :arg_s_import.im_type
	And im_id = :arg_s_import.im_id;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '更新外部物料对应描述失败,' + sqlca.SQLErrText
	GOTO ext
END IF

ext:
IF arg_if_commit THEN
	IF rslt = 1 THEN
		COMMIT;
	ELSE
		ROLLBACK;
	END IF
END IF
RETURN rslt

end function

public function integer uf_xls_to_import_mtrlcode (string arg_filepath, ref string arg_msg, boolean arg_ifcommit, boolean arg_ignore);Int rslt = 1

datastore arg_dw
arg_dw = Create datastore
arg_dw.DataObject = 'dw_import_mtrlname_xls'

IF f_xls_import_to_dw(arg_filepath, arg_dw, arg_msg) <> 1 THEN
	rslt = 0
	GOTO ext
END IF

Long i
Long im_type
String im_id
String mtrlcode
Long mtrlid
FOR i = 1 To arg_dw.RowCount()
	im_type = arg_dw.Object.im_type[i]
	im_id = Trim(arg_dw.Object.im_id[i])
	mtrlcode = Trim(arg_dw.Object.mtrlcode[i])
	IF im_id = '' Or mtrlcode = '' THEN CONTINUE
	SELECT mtrlid
		INTO :mtrlid
		FROM u_mtrldef
		Where mtrlcode = :mtrlcode;
	IF sqlca.SQLCode <> 0 THEN
		IF arg_ignore THEN CONTINUE
		rslt = 0
		arg_msg = '查询物料编码['+mtrlcode+']失败,' + sqlca.SQLErrText
		GOTO ext
	END IF
	UPDATE eb_import_mtrlname
		SET mtrlid = :mtrlid
		Where im_id = :im_id;
	IF sqlca.SQLCode <> 0 THEN
		IF arg_ignore THEN CONTINUE
		rslt = 0
		arg_msg = '更新网店产品定义['+im_id+']失败,' + sqlca.SQLErrText
		GOTO ext
	END IF
NEXT

ext:
Destroy arg_dw
IF arg_ifcommit THEN
	IF rslt = 1 THEN
		COMMIT;
	ELSE
		ROLLBACK;
	END IF
END IF

RETURN rslt

end function

public function integer uf_savemx (long arg_im_type, string arg_im_id, s_import_mtrlnamemx arg_mx[], ref string arg_msg);Int rslt = 1

DELETE From eb_import_mtrlnamemx Where im_type = :arg_im_type And im_id = :arg_im_id;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '删除旧的网店产品定义明细失败,' + sqlca.SQLErrText
	GOTO ext
END IF

Long i
FOR i = 1 To UpperBound(arg_mx)
	INSERT INTO eb_import_mtrlnamemx(im_type, im_id, pros, mtrlid, status_name, woodcode_name, pcode_name)
		Values(:arg_im_type, :arg_im_id, :arg_mx[i].pros, :arg_mx[i].mtrlid, :arg_mx[i].status_name, :arg_mx[i].woodcode_name, :arg_mx[i].pcode_name);
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '插入网店产品定义明细失败,' + sqlca.SQLErrText
		GOTO ext
	END IF
NEXT

ext:
IF rslt = 1 THEN
	COMMIT;
ELSE
	ROLLBACK;
END IF
RETURN rslt


end function

public function integer uf_csv_to_import_mtrlcode (string arg_filepath, ref s_import_mtrlname arg_import[], ref string arg_msg);Int rslt = 1
String ls_dscrp

IF Not FileExists(arg_filepath) THEN
	rslt = 0
	arg_msg = '文件' + arg_filepath + '不存在'
	GOTO ext
END IF

Long ll_pos, ll_pos2
ll_pos = Pos(arg_filepath, '\')
IF ll_pos > 0 THEN
	ll_pos2 = Pos(arg_filepath, '\', ll_pos + 1)
	DO WHILE ll_pos2 > 0
		ll_pos = ll_pos2
		ll_pos2 = Pos(arg_filepath, '\', ll_pos + 1)
	LOOP
	ls_dscrp = Mid(arg_filepath, ll_pos + 1)
ELSE
	ls_dscrp = arg_filepath
END IF


datastore ds_col100

/////////////建立映射//////////////////// //
ds_col100 = Create datastore
ds_col100.DataObject = 'dw_col100'

ds_col100.ImportFile(CSV!, arg_filepath)

datastore arg_dw
arg_dw = Create datastore
arg_dw.DataObject = 'dw_saletask_import_xls_taobao'

Long ll_s_col_count = 100
Long ll_d_col_count
ll_d_col_count = Long(arg_dw.Object.datawindow.Column.Count)
Long ll_map[] // 目标表与原表的映射，0为没有映射
String ls_colType[] // 目标表列类型

Long col_d, col_s
FOR col_d = 1 To ll_d_col_count
	String ls_col_title, ls_col_name
	ls_col_name = arg_dw.Describe('#' + String(col_d) + '.Name')
	ls_colType[col_d] = arg_dw.Describe('#' + String(col_d) + '.ColType')
	ls_col_title = arg_dw.Describe(ls_col_name + '_t.Text')
	ll_map[col_d] = 0
	FOR col_s = 1 To ll_s_col_count
		String ls_s_title
		ls_s_title = ds_col100.GetItemString(1, col_s)
		IF Lower(Trim(ls_col_title)) = Lower(Trim(ls_s_title)) THEN
			ll_map[col_d] = col_s
			EXIT
		END IF
	NEXT
NEXT

Long row_s, row_d
FOR row_s = 2 To ds_col100.RowCount( )
	IF Trim(ds_col100.GetItemString(row_s, 1),True) <> Trim(ds_col100.GetItemString(row_s - 1, 1),True) Or Trim(ds_col100.GetItemString(row_s, 2),True) <> Trim(ds_col100.GetItemString(row_s - 1, 2),True) THEN
		row_d = arg_dw.InsertRow(0)
	END IF
	
	FOR col_d = 1 To ll_d_col_count
		IF ll_map[col_d] = 0 THEN CONTINUE
		Any Value
		String ls_value
		ls_value = Trim(ds_col100.GetItemString(row_s, ll_map[col_d]),True)
		IF ls_value = '' Or ls_value = '-' THEN CONTINUE
		IF Lower(Left(ls_colType[col_d], 4)) = 'char' THEN
			Value = ls_value
		ELSEIF 	Lower(Left(ls_colType[col_d], 4)) = 'long' Or Lower(Left(ls_colType[col_d], 3)) = 'int' Or Lower(Left(ls_colType[col_d], 3)) = 'num' Or Lower(Left(ls_colType[col_d], 5)) = 'ulong' THEN
			Value = Long(ls_value)
		ELSEIF Lower(Left(ls_colType[col_d], 4)) = 'deci' Or  Lower(Left(ls_colType[col_d], 4)) = 'real' THEN
			Value = Dec(ls_value)
		ELSEIF Lower(Left(ls_colType[col_d], 4)) = 'date' THEN
			Value = DateTime(Date(Left(ls_value, 10)), Time(Mid(ls_value, 12)))
		END IF
		arg_dw.SetItem(row_d, col_d, Value)
	NEXT
NEXT
Destroy ds_col100
Long i, j, k = 0, cnt
Long ll_im_type
String ls_im_id
s_import_mtrlname s_empty
FOR i = 1 To arg_dw.RowCount()
	IF arg_dw.Object.mtrlcode[i] = '' Or IsNull(arg_dw.Object.mtrlcode[i]) THEN CONTINUE
	FOR j = 1 To UpperBound(arg_import)
		IF arg_dw.Object.mtrlcode[i] = arg_import[j].im_id THEN
			EXIT
		END IF
	NEXT
	IF j <= UpperBound(arg_import) THEN CONTINUE
	
	ll_im_type = 0
	ls_im_id = arg_dw.Object.mtrlcode[i]
	
	SELECT COUNT(0)
		INTO :cnt
		FROM eb_import_mtrlname
		WHERE im_type = :ll_im_type
		And im_id = :ls_im_id;
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '查询物料是否已定义失败,' + sqlca.SQLErrText
		GOTO ext
	END IF
	
	IF cnt > 0 THEN CONTINUE
	
	k++
	arg_import[k].im_type = 0
	arg_import[k].im_id = arg_dw.Object.mtrlcode[i]
	arg_import[k].eb_title = arg_dw.Object.mtrlname[i]
	arg_import[k].eb_price = arg_dw.Object.enprice[i]
	arg_import[k].numiid = arg_dw.Object.mtrlcode[i]
	arg_import[k].props = arg_dw.Object.props[i]
	arg_import[k].eb_locationcity = ls_dscrp
NEXT

Destroy arg_dw

ext:
RETURN rslt














end function

public function integer uf_save_from_web (s_import_mtrlname arg_s_import, ref string arg_msg, boolean arg_if_commit);Int rslt = 1
//
String ls_newid
String ls_ctg
Long ll_cnt

IF uf_reset(arg_s_import, arg_msg) <> 1 THEN
	rslt = 0
	GOTO ext
END IF

IF arg_s_import.im_id = '' Or arg_s_import.im_id = '0' THEN
	
	CHOOSE CASE arg_s_import.im_type
		CASE 0
			ls_ctg = 'MD'
		CASE ELSE
			rslt = 0
			arg_msg = '未定义类型' + String(arg_s_import.im_type)
			GOTO ext
	END CHOOSE
	
	ls_newid = pf_getid(ls_ctg, DateTime(Today(), Now()), False, sqlca)
	IF ls_newid = 'err' THEN
		rslt = 0
		arg_msg = '获取唯一码失败'
		GOTO ext
	END IF
	arg_s_import.im_id = ls_newid
	
END IF

SELECT COUNT(0)
	INTO :ll_cnt
	FROM eb_import_mtrlname
	WHERE im_type = :arg_s_import.im_type
	And im_id = :arg_s_import.im_id;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询商品导入的数量失败,' + sqlca.SQLErrText
	GOTO ext
END IF

IF ll_cnt = 0 THEN
	INSERT INTO eb_import_mtrlname
		(
		im_type,
		im_id,
		mtrlid,
		eb_num,
		eb_price,
		eb_type,
		eb_stuffStatus,
		eb_title,
		eb_dscrp,
		eb_locationstate,
		eb_locationcity,
		eb_cid,
		numIid,
		props,
		im_userid,
		status_name,
		woodcode_name,
		pcode_name,
		mx_flag)
		VALUES (
		:arg_s_import.im_type,
		:arg_s_import.im_id,
		:arg_s_import.mtrlid,
		:arg_s_import.eb_num,
		:arg_s_import.eb_price,
		:arg_s_import.eb_type,
		:arg_s_import.eb_stuffStatus,
		:arg_s_import.eb_title,
		:arg_s_import.eb_dscrp,
		:arg_s_import.eb_locationstate,
		:arg_s_import.eb_locationcity,
		:arg_s_import.eb_cid,
		:arg_s_import.numIid,
		:arg_s_import.props,
		:arg_s_import.im_userid,
		:arg_s_import.status_name,
		:arg_s_import.woodcode_name,
		:arg_s_import.pcode_name,
		:arg_s_import.mx_flag);
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '插入商品导入表失败,' + sqlca.SQLErrText
		GOTO ext
	END IF
ELSE
	
	
	UPDATE eb_import_mtrlname
		SET
		eb_num = :arg_s_import.eb_num,
		eb_price = :arg_s_import.eb_price,
		eb_type = :arg_s_import.eb_type,
		eb_stuffStatus = :arg_s_import.eb_stuffStatus,
		eb_title = :arg_s_import.eb_title,
		eb_locationstate = :arg_s_import.eb_locationstate,
		eb_locationcity = :arg_s_import.eb_locationcity,
		eb_cid = :arg_s_import.eb_cid,
		props = :arg_s_import.props
		WHERE (im_type = :arg_s_import.im_type)
		And (im_id = :arg_s_import.im_id);
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '更新商品导入表失败,' + sqlca.SQLErrText
		GOTO ext
	END IF
END IF

ext:
IF arg_if_commit THEN
	IF rslt = 1 THEN
		COMMIT;
	ELSE
		ROLLBACK;
	END IF
END IF
RETURN rslt

end function

public function integer uf_save (s_import_mtrlname arg_s_import, ref string arg_msg, boolean arg_if_commit);Int rslt = 1
//
String ls_newid
String ls_ctg
Long ll_cnt

IF uf_reset(arg_s_import, arg_msg) <> 1 THEN
	rslt = 0
	GOTO ext
END IF

IF arg_s_import.im_id = '' Or arg_s_import.im_id = '0' THEN
	
	CHOOSE CASE arg_s_import.im_type
		CASE 0
			ls_ctg = 'MD'
		CASE ELSE
			rslt = 0
			arg_msg = '未定义类型' + String(arg_s_import.im_type)
			GOTO ext
	END CHOOSE
	
	ls_newid = pf_getid(ls_ctg, DateTime(Today(), Now()), False, sqlca)
	IF ls_newid = 'err' THEN
		rslt = 0
		arg_msg = '获取唯一码失败'
		GOTO ext
	END IF
	arg_s_import.im_id = ls_newid
	
END IF

SELECT COUNT(0)
	INTO :ll_cnt
	FROM eb_import_mtrlname
	WHERE im_type = :arg_s_import.im_type
	And im_id = :arg_s_import.im_id;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询商品导入的数量失败,' + sqlca.SQLErrText
	GOTO ext
END IF

IF ll_cnt = 0 THEN
	INSERT INTO eb_import_mtrlname
		(
		im_type,
		im_id,
		mtrlid,
		eb_num,
		eb_price,
		eb_type,
		eb_stuffStatus,
		eb_title,
		eb_dscrp,
		eb_locationstate,
		eb_locationcity,
		eb_cid,
		numIid,
		props,
		im_userid,
		status_name,
		woodcode_name,
		pcode_name,
		mx_flag)
		VALUES (
		:arg_s_import.im_type,
		:arg_s_import.im_id,
		:arg_s_import.mtrlid,
		:arg_s_import.eb_num,
		:arg_s_import.eb_price,
		:arg_s_import.eb_type,
		:arg_s_import.eb_stuffStatus,
		:arg_s_import.eb_title,
		:arg_s_import.eb_dscrp,
		:arg_s_import.eb_locationstate,
		:arg_s_import.eb_locationcity,
		:arg_s_import.eb_cid,
		:arg_s_import.numIid,
		:arg_s_import.props,
		:arg_s_import.im_userid,
		:arg_s_import.status_name,
		:arg_s_import.woodcode_name,
		:arg_s_import.pcode_name,
		:arg_s_import.mx_flag);
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '插入商品导入表失败,' + sqlca.SQLErrText
		GOTO ext
	END IF
ELSE
	
	
	UPDATE eb_import_mtrlname
		SET
		mtrlid = :arg_s_import.mtrlid,
		eb_num = :arg_s_import.eb_num,
		eb_price = :arg_s_import.eb_price,
		eb_type = :arg_s_import.eb_type,
		eb_stuffStatus = :arg_s_import.eb_stuffStatus,
		eb_title = :arg_s_import.eb_title,
		eb_locationstate = :arg_s_import.eb_locationstate,
		eb_locationcity = :arg_s_import.eb_locationcity,
		eb_cid = :arg_s_import.eb_cid,
		props = :arg_s_import.props,
		status_name = :arg_s_import.status_name,
		woodcode_name = :arg_s_import.woodcode_name,
		pcode_name = :arg_s_import.pcode_name,
		mx_flag = :arg_s_import.mx_flag
		WHERE (im_type = :arg_s_import.im_type)
		And (im_id = :arg_s_import.im_id);
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '更新商品导入表失败,' + sqlca.SQLErrText
		GOTO ext
	END IF
END IF

ext:
IF arg_if_commit THEN
	IF rslt = 1 THEN
		COMMIT;
	ELSE
		ROLLBACK;
	END IF
END IF
RETURN rslt

end function

on uo_import_mtrlname.create
call super::create
TriggerEvent( this, "constructor" )
end on

on uo_import_mtrlname.destroy
TriggerEvent( this, "destructor" )
call super::destroy
end on