eCoWorksV3.git - Gitblit

xinyb

2022-06-20 ab4cffe8fb9df43040ee79e214661868369a8288

提交 \| 用户 \| age
bdf81b	1	package com.yc.MaintenanceFee.service.impl;
X	2
1e0503	3	import com.yc.MaintenanceFee.entiy.*;
bdf81b	4	import com.yc.MaintenanceFee.service.InvoiceService;
X	5	import com.yc.MaintenanceFee.utils.ReceiptUtils;
	6	import com.yc.action.grid.GridUtils;
	7	import com.yc.service.BaseService;
da667f	8	import org.apache.commons.lang3.StringUtils;
a66342	9	import org.springframework.jdbc.core.BeanPropertyRowMapper;
bdf81b	10	import org.springframework.stereotype.Service;
X	11
	12	import java.util.List;
	13	import java.util.Map;
1e0503	14	import java.util.regex.Matcher;
X	15	import java.util.regex.Pattern;
bdf81b	16
X	17	/**
	18	* 发票
	19	*
	20	* @USER: xinyb_
	21	* @DATE: 2022-03-07 09:15
	22	*/
	23	@Service
	24	public class InvoiceServiceImpl extends BaseService implements InvoiceService {
	25	/**
	26	* 获取已经在收款单生成发票单的信息（获取发票单）
	27	*
	28	* @return
	29	*/
	30	@Override
1e0503	31	public List<InvoiceInfo> getInvoiceInfo(String startDate, String endDate, String docCode, String dbId) throws Exception {
bdf81b	32	try {
X	33	String sql = "set nocount on ; \n";
	34	sql += " declare @cltCode varchar(50) ,@cltName varchar(50) \n" +
	35	" select @cltCode = c.cltCode,@cltName=c.cltName from t110203 c where c.dbId=" + dbId + "\n";
081022	36	sql += " select a.DocCode,a.formId,a.EnterDate as DocDate,a.DocStatus,\n" +
7ddb62	37	" case a.DocStatus when 0 then '已申请' when 100 then case when isnull(a.ReturnStatus,'') <> '' then isnull(a.ReturnStatus,'') else '已开票' end \n" +
X	38	" when 200 then '已退票' end as DocStatusName,\n" +
	39	" case when a.DocStatus = 100 and isnull(a.ReturnStatus,'') = '' then 1 else 0 end as isShowReturnButton,\n" +
	40	" a.CltCode,a.CltName,(case when isnull(a.invoiceMedium,'') = '' then '' else\n" +
	41	" case when a.invoiceMedium = 'PaperInvoice' then '纸质发票' else '电子发票' end end) as invoiceMedium ,\n" +
	42	" a.InvoiceType,a.billingNameType,a.taxpayerIdentificationNumber,a.billingName,\n" +
bdf81b	43	" a.billingPropertyAddress,a.billingTel,a.billingOpeningBank,a.billingBankAccount,a.billingCompany,a.receivingName,\n" +
a66342	44	" a.receivingTel,a.receivingAddress,a.sumTotalMoney2,a.sumNetMoney,a.sumVatMoney,b.vatRate,a.memo,b.docItem,a.refCode as matCode,b.matName,\n" +
82d67a	45	" b.UOM,b.digit,b.price,b.totalMoney,b.totalMoney2,b.otherMoney,b.itemMemo,a.AmountRecemoney,b.netprice \n" +
7ddb62	46	" from t150504H a \n" +
X	47	" join t150504D b on a.DocCode=b.docCode \n" +
	48	" where a.CltCode=@cltCode and b.refformid=120311";
1e0503	49	if (!StringUtils.isBlank(startDate)) {
X	50	sql += " and a.DocDate>=" + GridUtils.prossSqlParm(startDate);
	51	}
	52	if (!StringUtils.isBlank(endDate)) {
	53	sql += " and a.DocDate<=" + GridUtils.prossSqlParm(endDate);
	54	}
	55	if (!StringUtils.isBlank(docCode)) {
	56	sql += " and a.DocCode=" + GridUtils.prossSqlParm(docCode);
	57	}
7ddb62	58	sql += " order by a.DocCode desc";
bdf81b	59	List<Map<String, Object>> list = super.jdbcTemplate.queryForList(sql);
X	60	return ReceiptUtils.getInvoiceInfoList(list);
	61	} catch (Exception e) {
	62	throw e;
	63	}
	64	}
	65
	66	/**
	67	* 获取指定的发票单号信息
	68	*
	69	* @param docCode
	70	* @return
	71	* @throws Exception
	72	*/
	73	@Override
	74	public InvoiceInfo getInvoice(String docCode) throws Exception {
	75	try {
081022	76	String sql = " select a.DocCode,a.formId,a.EnterDate as DocDate,a.DocStatus,\n" +
7ddb62	77	" case a.DocStatus when 0 then '已申请' when 100 then case when isnull(a.ReturnStatus,'') <> '' then isnull(a.ReturnStatus,'') else '已开票' end \n" +
X	78	" when 200 then '已退票' end as DocStatusName,\n" +
	79	" case when a.DocStatus = 100 and isnull(a.ReturnStatus,'') = '' then 1 else 0 end as isShowReturnButton,\n" +
	80	"a.CltCode,a.CltName,(case when isnull(a.invoiceMedium,'') = '' then '' else\n" +
	81	" case when a.invoiceMedium = 'PaperInvoice' then '纸质发票' else '电子发票' end end) as invoiceMedium," +
	82	" a.InvoiceType,a.billingNameType,a.taxpayerIdentificationNumber,a.billingName,\n" +
bdf81b	83	" a.billingPropertyAddress,a.billingTel,a.billingOpeningBank,a.billingBankAccount,a.billingCompany,a.receivingName,\n" +
a66342	84	" a.receivingTel,a.receivingAddress,a.sumTotalMoney2,a.sumNetMoney,a.sumVatMoney,b.vatRate,a.memo,b.docItem,b.ShouKuanDoccode as matCode," +
7ddb62	85	" (select EnterDate from t150101H where docCode = b.ShouKuanDoccode) as docTime,b.matName,\n" +
41d07c	86	" b.UOM,b.digit,b.price,b.totalMoney,b.totalMoney2,b.otherMoney,b.itemMemo,a.AmountRecemoney,b.netprice,a.LogisticsCode,a.ShipperCode,\n" +
X	87	" (select ShipperName from t710165 where ShipperCode=a.ShipperCode) as ShipperName \n" +
7ddb62	88	" from t150504H a \n" +
bdf81b	89	" join t150504D b on a.DocCode=b.docCode where a.DocCode=" + GridUtils.prossSqlParm(docCode);
X	90	List<Map<String, Object>> list = super.jdbcTemplate.queryForList(sql);
	91	return ReceiptUtils.getInvoiceInfoList(list).get(0);
	92	} catch (Exception e) {
	93	throw e;
	94	}
	95	}
	96
	97	/**
1e0503	98	* 收款列表
3453fe	99	*
1e0503	100	* @param startDate
X	101	* @param endDate
	102	* @param docCode
	103	* @param dbId
	104	* @return
	105	* @throws Exception
	106	*/
	107	@Override
	108	public List<Collection> getCollection(String startDate, String endDate, String docCode, String dbId) throws Exception {
	109	try {
a66342	110	// String sql = "set nocount on ; \n" +
X	111	// " declare @cltCode varchar(50) ,@cltName varchar(50),@DocCode varchar(20) ,@Description varchar(max)\n" +
	112	// " declare @table table(DocCode varchar(20) primary key,DocDate datetime,PostDate datetime,\n" +
	113	// " Amount money,BillableAmount money,Description varchar(max))\n" +
	114	// " select @cltCode = c.cltCode,@cltName=c.cltName from t110203 c where c.dbId=82\n" +
	115	// " insert into @table(DocCode ,DocDate ,PostDate ,Amount ,BillableAmount ,Description)\n" +
	116	// " select a.DocCode,a.DocDate,a.EnterDate ,b.Amount,\n" +
	117	// " b.BillableAmount,'' as Description\n" +
	118	// " from t150101H a \n" +
	119	// " join (select a.DocCode ,sum(isnull(b.Amount,0)) as Amount,\n" +
	120	// " sum(isnull(b.Amount,0) - isnull(b.BillingAmount,0)+isnull(b.ReturnBillingAmount,0)) as BillableAmount \n" +
	121	// " from t150101H a join t150101D b on b.DocCode=a.DocCode \n" +
	122	// " where a.cltCode=@cltCode and a.refformid=120311 and a.DocStatus=100 \n" +
	123	// " and isnull(b.Amount,0) - isnull(b.BillingAmount,0)+isnull(b.ReturnBillingAmount,0) > 0 \n" +
	124	// " group by a.DocCode ) b on a.DocCode = b.DocCode \n" +
	125	// " where a.cltCode=@cltCode";
	126	// if (!StringUtils.isBlank(startDate)) {
	127	// sql += " and a.DocDate>=" + GridUtils.prossSqlParm(startDate);
	128	// }
	129	// if (!StringUtils.isBlank(endDate)) {
	130	// sql += " and a.DocDate<=" + GridUtils.prossSqlParm(endDate);
	131	// }
	132	// if (!StringUtils.isBlank(docCode)) {
	133	// sql += " and a.DocCode=" + GridUtils.prossSqlParm(docCode);
	134	// }
	135	// sql += "\n order by a.PostDate asc \n" +
	136	// " declare myInvoiceCur cursor for\n" +
	137	// " select DocCode from @table a \n" +
	138	// " open myInvoiceCur \n" +
	139	// " fetch next from myInvoiceCur into @DocCode \n" +
	140	// " while @@FETCH_STATUS = 0 \n" +
	141	// " begin \n" +
	142	// " set @Description = ''\n" +
	143	// " select @Description = case when isnull(@Description,'') <> '' then isnull(@Description,'') + ',' else '' end +\n" +
	144	// " isnull(c.MatName,'') + '/'+ cast(isnull(c.TotalMoney,0) as varchar(20)) +\n" +
	145	// " '*' + cast(c.Digit as varchar(20)) \n" +
	146	// " from t150101H a join t150101D b on b.DocCode=a.DocCode \n" +
	147	// " join t120301D c on c.doccode=b.SODoccode \n" +
	148	// " where a.cltCode=@cltCode and a.refformid=120311 and a.DocStatus=100 \n" +
	149	// " and isnull(b.Amount,0) - isnull(b.BillingAmount,0)+isnull(b.ReturnBillingAmount,0) > 0 \n" +
	150	// " and a.DocCode = @DocCode \n" +
	151	// " update a set Description = @Description from @table a where a.DocCode = @DocCode\n" +
	152	// " fetch next from myInvoiceCur into @DocCode \n" +
	153	// " end \n" +
	154	// " close myInvoiceCur\n" +
	155	// " deallocate myInvoiceCur\n" +
	156	// " select DocCode ,DocDate ,PostDate ,Amount ,BillableAmount ,Description \n" +
	157	// " from @table ";
	158	String sql = "set nocount on ; \n" +
	159	" declare @cltCode varchar(50) ,@cltName varchar(50),@DocCode varchar(20) ,@Description varchar(max)\n" +
	160	" declare @table table(DocCode varchar(20) primary key,DocDate datetime,PostDate datetime,\n" +
	161	" Amount money,BillableAmount money,Description varchar(max))\n" +
	162	" select @cltCode = c.cltCode,@cltName=c.cltName from t110203 c where c.dbId=" + dbId + " \n" +
	163	" insert into @table(DocCode ,DocDate ,PostDate ,Amount ,BillableAmount ,Description)\n" +
	164	" select a.DocCode,a.DocDate,a.EnterDate ,b.Amount,\n" +
	165	" b.BillableAmount,'' as Description\n" +
	166	" from t150101H a \n" +
	167	" join (select a.DocCode ,sum(isnull(b.Amount,0)) as Amount,\n" +
	168	" sum(isnull(b.Amount,0) - isnull(b.BillingAmount,0)+isnull(b.ReturnBillingAmount,0)) as BillableAmount \n" +
	169	" from t150101H a join t150101D b on b.DocCode=a.DocCode \n" +
	170	" where a.cltCode=@cltCode and a.refformid=120311 and a.DocStatus=100 \n" +
	171	" and isnull(b.Amount,0) - isnull(b.BillingAmount,0)+isnull(b.ReturnBillingAmount,0) > 0 \n" +
	172	" and not exists(select 1 from t150504D d join t150504H e on d.doccode = e.doccode \n" +
	173	" where b.DocCode = d.ShouKuanDoccode and b.rowid = d.ShouKuanRowid and e.DocStatus between 0 and 100 ) \n" +
	174	" group by a.DocCode ) b on a.DocCode = b.DocCode \n" +
	175	" where a.cltCode=@cltCode ";
1e0503	176	if (!StringUtils.isBlank(startDate)) {
X	177	sql += " and a.DocDate>=" + GridUtils.prossSqlParm(startDate);
	178	}
	179	if (!StringUtils.isBlank(endDate)) {
	180	sql += " and a.DocDate<=" + GridUtils.prossSqlParm(endDate);
	181	}
	182	if (!StringUtils.isBlank(docCode)) {
	183	sql += " and a.DocCode=" + GridUtils.prossSqlParm(docCode);
	184	}
a66342	185	sql += " \n order by a.PostDate asc \n" +
X	186	" declare myInvoiceCur cursor for\n" +
	187	" select DocCode from @table a \n" +
	188	" open myInvoiceCur \n" +
	189	" fetch next from myInvoiceCur into @DocCode \n" +
	190	" while @@FETCH_STATUS = 0 \n" +
	191	" begin \n" +
	192	" set @Description = ''\n" +
	193	" select @Description = case when isnull(@Description,'') <> '' then isnull(@Description,'') + ',' else '' end +\n" +
	194	" isnull(a.Description,'')\n" +
	195	" from t150101D a \t\n" +
	196	" where a.DocCode = @DocCode and isnull(a.Description,'') <> '' \n" +
	197	" update a set Description = @Description from @table a where a.DocCode = @DocCode\n" +
	198	" fetch next from myInvoiceCur into @DocCode \n" +
	199	" end \n" +
	200	" close myInvoiceCur\n" +
	201	" deallocate myInvoiceCur\n" +
	202	" select DocCode ,DocDate ,PostDate ,Amount ,BillableAmount ,Description \n" +
	203	" from @table\n";
	204	List<Collection> list = super.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Collection.class));
	205	return list;
1e0503	206	} catch (Exception e) {
X	207	throw e;
	208	}
	209	}
	210
	211	/**
bdf81b	212	* 收款单生成发票单(创建发票单)
X	213	*
	214	* @return
	215	*/
	216	@Override
3453fe	217	public String createInvoice(FPParam param) {
bdf81b	218	try {
3453fe	219	String docCode = String.join(",", param.getDocCode());
a66342	220	String sql = "set nocount on ; \n" +
82d67a	221	"declare @docCode varchar(50) =" + GridUtils.prossSqlParm(docCode) + "," +
7ddb62	222	" @invoiceMedium varchar(50) =" + GridUtils.prossSqlParm(param.getInvoiceMedium()) + "," +
X	223	" @seq int =" + param.getSeq() + ",@hdMemo varchar(200) = " + GridUtils.prossSqlParm(param.getHdMemo()) + "," +
3453fe	224	" @userCode varchar(50) = " + GridUtils.prossSqlParm(param.getUserCode()) + "," +
X	225	" @userName varchar(50) = " + GridUtils.prossSqlParm(param.getUserName()) + "," +
ab4cff	226	" @tel2 varchar(50) = " + GridUtils.prossSqlParm(param.getTel2()) + "," +
X	227	" @email varchar(200) = " + GridUtils.prossSqlParm(param.getEmail()) + "," +
	228	" @formid int = 150504, @linkdocinfo varchar(2000) = '',@Memo varchar(2000) = '' \n";
	229	if (StringUtils.isNotBlank(param.getTel2()) && StringUtils.isNotBlank(param.getEmail())) {
	230	sql += " exec p150134 @doccode=@docCode,@formid=@formid,@usercode=@userCode,@username=@userName," +
	231	" @InvoiceMedium=@invoiceMedium,@seq=@seq,@HDmemo=@hdMemo,@Tel2=@tel2,@Email=@email," +
	232	" @Memo=@Memo output ,@linkdocinfo=@linkdocinfo output\n" +
	233	" select @linkdocinfo as fpCode";
	234	} else {
	235	sql += " exec p150133 @doccode=@docCode,@formid=@formid,@usercode=@userCode,@username=@userName," +
	236	" @InvoiceMedium=@invoiceMedium,@seq=@seq,@HDmemo=@hdMemo,@Memo=@Memo output ,@linkdocinfo=@linkdocinfo output\n" +
	237	" select @linkdocinfo as fpCode";
	238	}
bdf81b	239	return super.jdbcTemplate.queryForObject(sql, String.class);
X	240	} catch (Exception e) {
	241	throw e;
	242	}
	243	}
	244
	245	/**
	246	* 获取发票抬头信息
	247	*
	248	* @param dbId
	249	* @return
	250	*/
	251	@Override
	252	public Map<String, Object> getInvoiceHeader(String dbId) {
	253	try {
	254	String sql = "set nocount on ; \n";
	255	sql += " declare @cltCode varchar(50) ,@cltName varchar(50) \n" +
	256	" select @cltCode = c.cltCode,@cltName=c.cltName from t110203 c where c.dbId=" + dbId + "\n";
	257	sql += " if isNull(@cltCode,0)=0\n" +
	258	" begin\n" +
	259	" raiserror('获取不到当前系统的客户编号(cltCode)',16,1)\n" +
	260	" return \n" +
	261	" end \n";
1e0503	262	sql += "select InvoiceType as invType,billingNameType,taxpayerIdentificationNumber,billingName,\n" +
ab4cff	263	" billingPropertyAddress,billingTel,billingOpeningBank,billingBankAccount,tel2,email \n" +
bdf81b	264	" from t110203 where cltCode=@cltCode";
X	265	return super.jdbcTemplate.queryForMap(sql);
	266	} catch (Exception e) {
	267	throw e;
	268	}
	269	}
	270
	271	/**
	272	* 编辑发票抬头信息
	273	*
	274	* @param header
	275	* @return
	276	*/
	277	@Override
	278	public Integer saveInvoiceHeader(Header header, String dbId) {
	279	try {
	280	String sql = "set nocount on ; \n";
	281	sql += " declare @cltCode varchar(50) ,@cltName varchar(50) \n" +
	282	" select @cltCode = c.cltCode,@cltName=c.cltName from t110203 c where c.dbId=" + dbId + "\n";
	283	sql += " if isNull(@cltCode,0)=0\n" +
	284	" begin\n" +
	285	" raiserror('获取不到当前系统的客户编号(cltCode)',16,1)\n" +
	286	" return \n" +
	287	" end \n";
	288	sql += " update a set a.InvoiceType=" + GridUtils.prossSqlParm(header.getInvType()) + "," +
	289	"a.billingNameType=" + GridUtils.prossSqlParm(header.getBillingNameType()) + "," +
	290	" a.taxpayerIdentificationNumber=" + GridUtils.prossSqlParm(header.getTaxpayerIdentificationNumber()) + "," +
	291	" a.billingName=" + GridUtils.prossSqlParm(header.getBillingName()) + ",\n" +
	292	" a.billingPropertyAddress=" + GridUtils.prossSqlParm(header.getBillingPropertyAddress()) + "," +
	293	" a.billingTel=" + GridUtils.prossSqlParm(header.getBillingTel()) + "," +
	294	" a.billingOpeningBank=" + GridUtils.prossSqlParm(header.getBillingOpeningBank()) + "," +
	295	" a.billingBankAccount=" + GridUtils.prossSqlParm(header.getBillingBankAccount()) + "\n" +
	296	" from t110203 a where a.cltCode=@cltCode \n" +
	297	" select 1 \n";
	298	return super.jdbcTemplate.queryForObject(sql, Integer.class);
	299	} catch (Exception e) {
	300	throw e;
	301	}
	302
	303	}
	304
	305	/**
	306	* 编辑退票信息
	307	*
	308	* @param outTicket
	309	* @return
	310	*/
	311	@Override
3453fe	312	public String saveOutTicket(OutTicket outTicket) {
bdf81b	313	try {
X	314	String sql = "set nocount on ; \n";
3453fe	315	sql += "declare @doccode varchar(20) = " + GridUtils.prossSqlParm(outTicket.getFpCode()) + "," +
081022	316	" @formId int ="+outTicket.getFormId()+"," +
3453fe	317	" @ReceivingName varchar(50) = " + GridUtils.prossSqlParm(outTicket.getReceivingName()) + "," +
X	318	" @ReceivingTel varchar(50) = " + GridUtils.prossSqlParm(outTicket.getReceivingTel()) + " ," +
	319	" @ReceivingAddress varchar(50) = " + GridUtils.prossSqlParm(outTicket.getReceivingAddress()) + "," +
	320	" @Reason varchar(50) = " + GridUtils.prossSqlParm(outTicket.getReason()) + "," +
51d5ac	321	" @LogisticsCode varchar(50) = " + GridUtils.prossSqlParm(outTicket.getLogisticsCode()) + "," +
X	322	" @ShipperCode varchar(50) = " + GridUtils.prossSqlParm(outTicket.getShipperCode()) + "," +
3453fe	323	" @HDMemo varchar(2000) = " + GridUtils.prossSqlParm(outTicket.getHDMemo()) + "," +
X	324	" @userCode varchar(20) = " + GridUtils.prossSqlParm(outTicket.getUserCode()) + "," +
	325	" @username varchar(50) = " + GridUtils.prossSqlParm(outTicket.getUserName()) + "," +
	326	" @memo varchar(200) = '' , @linkdocinfo varchar(200) = '' \n";
	327	sql += " exec p150509 @doccode=@doccode,@formid=@formId,@ReceivingName=@ReceivingName,@ReceivingTel=@ReceivingTel," +
51d5ac	328	"@ReceivingAddress=@ReceivingAddress,@Reason=@Reason,@LogisticsCode=@LogisticsCode,@ShipperCode=@ShipperCode," +
3453fe	329	"@HDMemo=@HDMemo,@userCode=@userCode,@username=@username,@memo=@memo,@linkdocinfo=@linkdocinfo output\n" +
X	330	"select @linkdocinfo";
bdf81b	331	return super.jdbcTemplate.queryForObject(sql, String.class);
X	332	} catch (Exception e) {
	333	throw e;
	334	}
	335	}
	336
	337	/**
	338	* 获取客户地址（获取地址）
	339	*
	340	* @param dbId
	341	* @return
	342	*/
	343	@Override
	344	public List<Map<String, Object>> getAddressInfo(String dbId) {
	345	try {
	346	String sql = "set nocount on \n" +
1e0503	347	"select a.seq,a.cltCode,a.cltName,a.linkMan,a.tel,a.postCode,a.countryId," +
X	348	"a.provinceZoneId,a.provinceName,a.cityZoneId,a.cityName," +
	349	"a.countyZoneId,a.countyName,a.street,a.propertyID,a.propertyName,a.building,a.unit,a.house,a.propertyAddress," +
7ddb62	350	"isnull(a.isDefaultSelected ,0) as isDefaultSelected " +
X	351	"from t110209 a left join t110203 b on a.cltCode=b.cltCode where b.dbid=" +
bdf81b	352	GridUtils.prossSqlParm(dbId);
a66342	353	sql += " order by a.seq desc";
bdf81b	354	return super.jdbcTemplate.queryForList(sql);
da667f	355	} catch (Exception e) {
X	356	throw e;
	357	}
	358	}
	359
	360	/**
	361	* 获取指定的地址信息
	362	*
	363	* @param dbId
	364	* @param seq
	365	* @return
	366	*/
	367	@Override
	368	public Map<String, Object> selectAddress(String dbId, Integer seq) {
	369	try {
	370	String sql = "set nocount on \n" +
1e0503	371	"select a.seq,a.cltCode,a.cltName,a.linkMan,a.tel,a.postCode,a.countryId," +
X	372	"a.provinceZoneId,a.provinceName,a.cityZoneId,a.cityName," +
	373	"a.countyZoneId,a.countyName,a.street,a.propertyID,a.propertyName,a.building,a.unit,a.house,a.propertyAddress," +
	374	"isnull(a.isDefaultSelected ,0) as isDefaultSelected from t110209 a left join t110203 b on a.cltCode=b.cltCode and a.seq=" + seq + "" +
da667f	375	" where b.dbid=" + GridUtils.prossSqlParm(dbId);
X	376	return super.jdbcTemplate.queryForMap(sql);
bdf81b	377	} catch (Exception e) {
X	378	throw e;
	379	}
	380	}
	381
	382	/**
	383	* 编辑地址信息（保存，修改，删除）
	384	*
	385	* @param address
	386	* @param dbId
	387	* @return
	388	*/
	389	@Override
	390	public Integer saveAddress(Address address, String dbId) {
	391	try {
	392	Integer cont;
	393	String sql = "set nocount on ; \n";
	394	sql += " declare @cltCode varchar(50) ,@cltName varchar(50) \n";
	395	sql += " declare @table table (cltCode varchar(50),cltName varchar(50),LinkMan varchar(50),Tel varchar(50)," +
	396	" PostCode varchar(50),CountryId int,ProvinceZoneId int,CityZoneId int,CountyZoneId int,\n" +
da667f	397	" CountryName varchar(50),ProvinceName varchar(50),CityName varchar(50),CountyName varchar(50), \n" +
bdf81b	398	" Street varchar(200),PropertyID int,PropertyName varchar(50),Building varchar(50),Unit varchar(50)," +
1e0503	399	" House varchar(50),PropertyAddress varchar(200),FullAddress varchar(200),isDefaultSelected int) \n" +
7ddb62	400	" declare @CountryName varchar(50),@ProvinceName varchar(50),@CityName varchar(50),@CountyName varchar(50)," +
X	401	" @address varchar(100) \n";
bdf81b	402	sql += " select @cltCode = c.cltCode,@cltName=c.cltName from t110203 c where c.dbId=" + dbId + "\n";
X	403	sql += " if isNull(@cltCode,0)=0\n" +
	404	" begin\n" +
	405	" raiserror('获取不到当前系统的客户编号(cltCode)',16,1)\n" +
	406	" return \n" +
	407	" end \n";
	408	if (address.getOperation() > 0) {
	409	//执行添加或修改(临时表)
1e0503	410	sql += " select @ProvinceName = areaName from t110202 where zoneid = " + address.getProvinceZoneId() + " \n" +
X	411	" select @CityName = areaName from t110202 where zoneid = " + address.getCityZoneId() + " \n" +
	412	" select @CountyName = areaName from t110202 where zoneid = " + address.getCountyZoneId() + " \n";
7ddb62	413	sql += " select @address = @ProvinceName+@CityName+@CountyName+" + GridUtils.prossSqlParm(address.getStreet());
da667f	414	sql += " insert into @table(cltCode,cltName,LinkMan,Tel,PostCode,CountryId,ProvinceZoneId,ProvinceName,CityZoneId,CityName," +
X	415	" CountyZoneId,CountyName," +
1e0503	416	"Street,PropertyID,PropertyName,Building,Unit,House,PropertyAddress,FullAddress,isDefaultSelected) \n" +
bdf81b	417	" values(@cltCode,@cltName," + GridUtils.prossSqlParm(address.getLinkMan()) + "," + GridUtils.prossSqlParm(address.getTel()) + "," +
da667f	418	GridUtils.prossSqlParm(address.getPostCode()) + "," + address.getCountryId() + "," + address.getProvinceZoneId() + ",@ProvinceName," +
X	419	address.getCityZoneId() + ",@CityName," + address.getCountyZoneId() + ",@CountyName," + GridUtils.prossSqlParm(address.getStreet()) + "," +
bdf81b	420	address.getPropertyID() + "," + GridUtils.prossSqlParm(address.getPropertyName()) + "," +
X	421	GridUtils.prossSqlParm(address.getBuilding()) + "," + GridUtils.prossSqlParm(address.getUnit()) + "," +
7ddb62	422	GridUtils.prossSqlParm(address.getHouse()) + ",@address,@address," + address.getIsDefaultSelected() + ") \n";
1e0503	423	if (address.getIsDefaultSelected() > 0) {//把客户地址默认值设为0
X	424	sql += " update a set a.isDefaultSelected=0 from t110209 a where a.cltCode=@cltCode \n";
	425	}
bdf81b	426	if (address.getOperation() == 1) { //添加
1e0503	427	sql += " insert into t110209(cltCode,cltName,LinkMan,Tel,PostCode,CountryId,ProvinceZoneId,ProvinceName,CityZoneId,CityName," +
da667f	428	" CountyZoneId,CountyName," +
1e0503	429	" Street,PropertyID,PropertyName,Building,Unit,House,PropertyAddress,FullAddress,isDefaultSelected) \n" +
da667f	430	" select a.cltCode,a.cltName,a.LinkMan,a.Tel,a.PostCode,a.CountryId,a.ProvinceZoneId,a.ProvinceName," +
X	431	" a.CityZoneId,a.CityName,a.CountyZoneId,a.CountyName," +
1e0503	432	" a.Street,a.PropertyID,a.PropertyName,a.Building,a.Unit,a.House,a.PropertyAddress,a.FullAddress,a.isDefaultSelected \n" +
bdf81b	433	" from @table a";
X	434	} else if (address.getOperation() == 2) {//修改
7ddb62	435	sql += "update a set Tel=b.Tel,Street=b.Street,PropertyAddress=b.PropertyAddress,FullAddress=b.FullAddress,PostCode=b.PostCode," +
da667f	436	" ProvinceZoneId=b.ProvinceZoneId,ProvinceName=b.ProvinceName,CityZoneId=b.CityZoneId,CityName=b.CityName," +
1e0503	437	"CountyZoneId=b.CountyZoneId,CountyName=b.CountyName,LinkMan=b.LinkMan";
X	438	if (address.getIsDefaultSelected() > 0) {
	439	sql += ",isDefaultSelected=b.isDefaultSelected ";
	440	}
	441	sql += " from t110209 a join @table b on a.cltCode=b.cltCode and a.seq=" + address.getSeq() + "\n";
bdf81b	442	}
X	443	} else {
	444	//执行删除
	445	sql += "delete a from t110209 a where a.cltCode=@cltCode and a.seq=" + address.getSeq() + "\n";
1e0503	446	sql += "declare @seq int\n" +
X	447	" select top 1 @seq=seq from t110209 where cltCode=@cltCode \n" +
	448	" if(isnull(@seq,0)>0) \n" +
	449	" begin \n" +
	450	" update a set a.isDefaultSelected=1 from t110209 a where a.seq=@seq \n" +
	451	" end \n";
bdf81b	452	}
X	453	sql += " select 1 \n";
	454	cont = super.jdbcTemplate.queryForObject(sql, Integer.class);
	455	return cont;
	456	} catch (Exception e) {
	457	throw e;
	458	}
	459	}
da667f	460
X	461	/**
	462	* 获取区域信息
	463	*
	464	* @param rowId
	465	* @return
	466	*/
	467	@Override
	468	public List<Map<String, Object>> getRegion(String rowId) {
	469	try {
1e0503	470	Pattern pattern = Pattern.compile("[0-9]*");
X	471	Matcher isNum = pattern.matcher(rowId);
	472
da667f	473	String sql = "set nocount on ;\n" +
1e0503	474	" declare @rowId varchar(20) \n";
X	475	if (!StringUtils.isBlank(rowId) && isNum.matches()) {//是数字
3453fe	476	sql += " select @rowId=rowid from t110202 where zoneId = " + rowId + "\n";
X	477	} else {
	478	sql += " select @rowId=" + GridUtils.prossSqlParm(rowId) + "\n";
1e0503	479	}
X	480	sql += " select rowid,treecontrol,isnull(parentrowid,'') as parentrowid,zoneId,areaid,areaname \n" +
	481	" from t110202 as b";
da667f	482	if (StringUtils.isBlank(rowId)) {
X	483	sql += " where (b.parentrowid ='' or b.parentrowid is null or not exists (select 1 from t110202 c where b.parentrowid = c.rowid))";
	484	} else {
1e0503	485	sql += " where b.parentrowid = @rowId";
da667f	486	}
X	487	return super.jdbcTemplate.queryForList(sql);
	488	} catch (Exception e) {
	489	throw e;
	490	}
	491	}
3453fe	492
X	493	/**
	494	* 获取开票金额
	495	*
	496	* @param dbId
	497	* @return
	498	*/
	499	@Override
7ddb62	500	public double getInvoiceAmount(String dbId) {
3453fe	501	try {
X	502	String sql = "set nocount on ; \n";
	503	sql += " declare @cltCode varchar(50) ,@cltName varchar(50) \n" +
	504	" select @cltCode = c.cltCode,@cltName=c.cltName from t110203 c where c.dbId=" + dbId + "\n";
	505	sql += " if isNull(@cltCode,0)=0\n" +
	506	" begin\n" +
	507	" raiserror('获取不到当前系统的客户编号(cltCode)',16,1)\n" +
	508	" return \n" +
	509	" end \n";
	510	sql += "declare @balance money \n" +
a66342	511	"select @balance = sum(isnull(a.Amount,0) - isnull(a.BillingAmount,0)+isnull(a.ReturnBillingAmount,0))\n" +
3453fe	512	"from t150101D a join t150101h b on a.doccode = b.doccode \n" +
X	513	"where b.cltcode = @cltCode and docstatus = 100 \n" +
82d67a	514	"declare @FaPiaoAmount money \n" +
X	515	"select @FaPiaoAmount = sum(isnull(a.totalmoney,0))\n" +
	516	"from t150504D a join t150504H b on a.DocCode = b.DocCode \n" +
	517	"where b.CltCode = @CltCode and isnull(b.DocStatus,0) < 100 \n" +
	518	"select round(isnull(@balance,0) - isnull(@FaPiaoAmount,0),2)";
7ddb62	519	return jdbcTemplate.queryForObject(sql, double.class);
X	520	} catch (Exception e) {
	521	throw e;
	522	}
a66342	523	}
X	524
	525	@Override
	526	public List<Map<String, Object>> getInvoiceViewData(String fpDocCode) {
51d5ac	527	try {
a66342	528	// String sql=" select '信息技术服务软件维护' as description,a.doccode,b.vatrate,\n" +
X	529	// " sum(isnull(b.netmoney,0)) as netmoney,\n" +
	530	// " sum(isnull(b.vatmoney,0)) as vatmoney,\n" +
	531	// " sum(isnull(b.totalmoney,0)) as totalmoney,\n" +
	532	// " sum(isnull(b.netmoney,0)) * 1.00000 / (1+isnull(b.vatrate,0)) as netprice\n" +
	533	// " from t150504h a join t150504d b on a.doccode = b.doccode \n" +
	534	// " where a.doccode = "+GridUtils.prossSqlParm(fpDocCode)+" \n" +
	535	// " group by a.doccode,b.vatrate";
51d5ac	536	String sql = " select a.doccode,a.InvoiceType,\n" +
X	537	" a.BillingName,a.BillingTel,a.TaxpayerIdentificationNumber,a.BillingPropertyAddress,\n" +
	538	" a.BillingOpeningBank,a.BillingBankAccount,b.vatrate,\n" +
	539	" sum(isnull(b.netmoney,0)) as netmoney,\n" +
	540	" sum(isnull(b.vatmoney,0)) as vatmoney,\n" +
	541	" sum(isnull(b.totalmoney,0)) as totalmoney,\n" +
	542	" sum(isnull(b.netmoney,0)) /1.000000 as netprice\n" +
	543	" from t150504h a join t150504d b on a.doccode = b.doccode \n" +
	544	" where a.doccode = " + GridUtils.prossSqlParm(fpDocCode) + " \n" +
	545	" group by a.doccode,b.vatrate, a.InvoiceType,\n" +
	546	" a.BillingName,a.BillingTel,a.TaxpayerIdentificationNumber,a.BillingPropertyAddress,\n" +
	547	" a.BillingOpeningBank,a.BillingBankAccount";
	548	return jdbcTemplate.queryForList(sql);
	549	} catch (Exception e) {
	550	throw e;
	551	}
7ddb62	552	}
X	553
	554	/**
	555	* 预览发票信息
	556	*
	557	* @param docCode
	558	* @param dbID
	559	* @return
	560	*/
	561	@Override
	562	public Map<String, Object> getInvoiceView(String docCode, String dbID) {
	563	try {
	564	String sql = "set nocount on ; \n";
	565	sql += " declare @cltCode varchar(50) ,@cltName varchar(50) \n" +
	566	" select @cltCode = c.cltCode,@cltName=c.cltName from t110203 c where c.dbId=" + dbID + "\n";
	567	sql += " if isNull(@cltCode,0)=0\n" +
	568	" begin\n" +
	569	" raiserror('获取不到当前系统的客户编号(cltCode)',16,1)\n" +
	570	" return \n" +
	571	" end \n";
	572	sql += "declare @doccode varchar(100) = " + GridUtils.prossSqlParm(docCode) + ", @amount money,@taxamount money," +
82d67a	573	"@price money,@amounts money,@totalamount money\n" +
a66342	574	"select @amount=sum(isnull(a.Amount,0) - isnull(a.BillingAmount,0)+isnull(a.ReturnBillingAmount,0)) \n" +
X	575	"from t150101D a where a.DocCode in (select list from GetInStr(@doccode)) and a.cltcode=@cltCode --价税合计 \n" +
da8e50	576	"select @price=round(@amount/(1+3/100.00),2)--不含税单价\n" +
82d67a	577	"select @amounts=round(@price*1,2)--不含税金额\n" +
a66342	578	"select @taxamount=round(@amount - @amount/(1+3/100.00),2)--税额\n" +
82d67a	579	"select @totalamount=round(@amounts + @taxamount,2)--价税合计\n" +
X	580	"select isnull(@amounts,0) as amount,isnull(@taxamount,0) as taxAmount,@price as price,isnull(@totalamount,0) as totalAmount";
7ddb62	581	return super.jdbcTemplate.queryForMap(sql);
3453fe	582	} catch (Exception e) {
X	583	throw e;
	584	}
	585	}
9a9585	586
a66342	587
9a9585	588	/**
X	589	* 获取退票原因列
a66342	590	*
9a9585	591	* @return
X	592	*/
	593	@Override
	594	public List<Map<String, Object>> getReason() {
	595	try {
a66342	596	String sql = "select dictvalue,interValue from _sysdict where dictid = -15050810 order by sequence asc";
9a9585	597	return super.jdbcTemplate.queryForList(sql);
a66342	598	} catch (Exception e) {
51d5ac	599	return null;
X	600	}
	601	}
	602
	603	/**
	604	* 获取快递公司列表
	605	* @return
	606	*/
	607	public List<Map<String, Object>> getShipperCode() {
	608	try {
	609	String sql = "select shipperCode,shipperName from t710165 order by AreaName asc,SortBy asc,FirstAlphabet asc";
	610	return jdbcTemplate.queryForList(sql);
	611	} catch (Exception e) {
	612	return null;
9a9585	613	}
X	614	}
bdf81b	615	}