/// <summary>
/// get sap A501 & A306 ladder price
/// </summary>
/// <returns></returns>
private SapMiddleData GetSapPriceList()
{
//抓全部資料，由於不是參數化查詢，第二個參數傳null
string _SqlA501 = @"select a.KUNNR ,d.name1,a.ZZENDCUST,e.name1 as zname1,a.MATNR ,b.KONWA,c.KSTBM,c.KBETR,c.KLFN1
,a.KNUMH ,b.KPEIN ,a.DATAB ,a.kschl from sapabap1.A501 a join sapabap1.KONP b on a.KNUMH=b.KNUMH join sapabap1.KONM c on a.KNUMH=c.KNUMH join sapabap1.kna1 d on a.kunnr=d.kunnr join sapabap1.kna1 e on a.ZZENDCUST=e.kunnr
where DATBI='99991231' ";
if (txtDate.Text != "")
{
_SqlA501 += " and DATAB > " + txtDate.Text;
}
_SqlA501 += " order by a.knumh,a.KUNNR,a.ZZENDCUST,c.KLFN1,a.matnr";
DataTable dtA501 = OdbcHelper.GetDataTableText(_SqlA501, null);
string _SqlA306 = @"select a.KUNNR ,d.name1, a.MATNR, b.KONWA ,c.KSTBM,c.KBETR,c.KLFN1 ,a.KNUMH ,b.KPEIN,a.DATAB
,a.kschl from sapabap1.A305 a join sapabap1.KONP b on a.KNUMH=b.KNUMH join sapabap1.KONM c on a.KNUMH=c.KNUMH
join sapabap1.kna1 d on a.kunnr=d.kunnr where DATBI='99991231' ";
if (txtDate.Text != "")
{
_SqlA306 += " and DATAB > " + txtDate.Text;
}
_SqlA306 += " order by a.knumh,a.KUNNR,a.matnr,c.KLFN1";
DataTable dtA306 = OdbcHelper.GetDataTableText(_SqlA306, null);
dtA501.Merge(dtA306);
SapPrice _SapPrice = new SapPrice();
SapMiddleData _SapMiddleData = _SapPrice.Parse(dtA501);
return(_SapMiddleData);
}
/// <summary>
/// only get buyer 1000 data
/// country name in english
/// US/CN need mapping region
/// </summary>
/// <returns></returns>
private List <WebApi.DataModel.CustomModel.SAP.BpXY> GetBpXYList()
{
List <WebApi.DataModel.CustomModel.SAP.BpXY> _BpXYList = new List <WebApi.DataModel.CustomModel.SAP.BpXY>();
BLL.BpXY _BpXY = new BLL.BpXY();
string _Sql = @" select a.kunnr,c.BU_SORT1,c.BU_SORT2,vkorg,d.NAME_CO ,a.KDGRP ,b.land1 ,e.landx,d.REGION
from sapabap1.KNVV a
join sapabap1.kna1 b on a.kunnr=b.kunnr
join sapabap1.but000 c on a.kunnr = c.partner
JOIN sapabap1.ADRC d ON d.ADDRNUMBER = b.ADRNR
join sapabap1.t005t e on b.land1=e.land1
where c.bu_group='1000' and e.spras='E' and e.mandt='888'
and a.loevm <> 'X' ";
DataTable dt = OdbcHelper.GetDataTableText(_Sql, null);
string _SqlRegion = @" select bland,bezei from sapabap1.T005U where mandt='888' and spras='E' and (land1='CN'
or land1='US') ";
DataTable dtRegion = OdbcHelper.GetDataTableText(_SqlRegion, null);
_BpXYList = _BpXY.Parse(dt, dtRegion);
string _SqlCE11000 = @" select ERLOS,GJAHR,KNDNR from sapabap1.CE11000
WHERE PALEDGER = '01'
AND ( VRGAR = 'F' OR VRGAR = 'Y' ) ";
DataTable dtCE11000 = OdbcHelper.GetDataTableText(_SqlCE11000, null);
_BpXYList = _BpXY.ParseRevenue(_BpXYList, dtCE11000);
return(_BpXYList);
}
/// <summary>
/// get customer address data no need content end cust(20001XXX)
/// except customer be delete
/// --d.bu_group='1000'
/// --and
/// </summary>
/// <returns></returns>
private List <WebApi.DataModel.CustomModel.SAP.BpAddressAll> GetBpAddressAllList()
{
string _SqlKNA1 = @" SELECT a.land1, a.kunnr,a.name1,str_suppl1,str_suppl2 ,b.street
,b.LOCATION
,b.POST_CODE1,b.CITY1
,b.COUNTRY,b.REGION,b.TIME_ZONE
, CITY2
,str_suppl3
,HOME_CITY
,c.VKORG
from sapabap1.kna1 a
join sapabap1.adrc b on a.adrnr=b.ADDRNUMBER
join sapabap1.knvv c on a.kunnr=c.kunnr
join sapabap1.but000 d on a.kunnr = d.partner
where
d.bu_group='1000'
and
c.loevm <> 'X'
order by a.kunnr ";
DataTable dtKNA1 = OdbcHelper.GetDataTableText(_SqlKNA1, null);
IList <WebApi.DataModel.CustomModel.SAP.BpAddressAll> _BpAddressAllIList = dtKNA1.ToList <WebApi.DataModel.CustomModel.SAP.BpAddressAll>();
List <WebApi.DataModel.CustomModel.SAP.BpAddressAll> _BpAddressAllList = _BpAddressAllIList as List <WebApi.DataModel.CustomModel.SAP.BpAddressAll>;
return(_BpAddressAllList);
}
private List <WebApi.DataModel.CustomModel.SAP.BpCountry> GetCountryList()
{
string _Sql = @"select SPRAS,LAND1,LANDX from sapabap1.T005T
where ( SPRAS='1' or SPRAS='M' or SPRAS='E' ) and MANDT='888' ";
DataTable dt = OdbcHelper.GetDataTableText(_Sql, null);
IList <WebApi.DataModel.CustomModel.SAP.BpCountry> _BpCountryIList = dt.ToList <WebApi.DataModel.CustomModel.SAP.BpCountry>();
List <WebApi.DataModel.CustomModel.SAP.BpCountry> _BpCountryList = _BpCountryIList as List <WebApi.DataModel.CustomModel.SAP.BpCountry>;
return(_BpCountryList);
}
private List <WebApi.DataModel.CustomModel.SAP.BpSales> GetBpSalesList()
{
string _SqlKNA1 = @" SELECT a.vkorg, a.kunnr ,c.name1,c.name2,SNAME
FROM sapabap1.KNVP a
INNER JOIN sapabap1.PA0001 b ON b.PERNR = a.PERNR
join sapabap1.kna1 c on a.kunnr=c.kunnr ";
DataTable dtKNA1 = OdbcHelper.GetDataTableText(_SqlKNA1, null);
IList <WebApi.DataModel.CustomModel.SAP.BpSales> _BpSalesIList = dtKNA1.ToList <WebApi.DataModel.CustomModel.SAP.BpSales>();
List <WebApi.DataModel.CustomModel.SAP.BpSales> _BpSalesList = _BpSalesIList as List <WebApi.DataModel.CustomModel.SAP.BpSales>;
return(_BpSalesList);
}
private List <WebApi.DataModel.CustomModel.SAP.BpAddress> GetBpAddressList()
{
BLL.BpAddress _BpAddress = new BLL.BpAddress();
List <WebApi.DataModel.CustomModel.SAP.BpAddress> _BpAddressList = new List <WebApi.DataModel.CustomModel.SAP.BpAddress>();
string _SqlKNA1 = @" SELECT kunnr,a.name1,str_suppl1,str_suppl2
from sapabap1.kna1 a
join sapabap1.adrc b on a.adrnr=b.ADDRNUMBER ";
DataTable dtKNA1 = OdbcHelper.GetDataTableText(_SqlKNA1, null);
_BpAddressList = _BpAddress.Parse(dtKNA1);
return(_BpAddressList);
}
/// <summary>
/// test sap_proc='S'
/// </summary>
/// <returns></returns>
private List <EdiStatus> GetSapEdiStatusList()
{
string _Sql = @"select
mandt,vbeln,sap_proc,seq,laeda,aenam
from sapabap1.zsdt046
where sap_proc = '' ";
DataTable dt = OdbcHelper.GetDataTableText(_Sql, null);
IList <EdiStatus> _EdiStatusIList = dt.ToList <EdiStatus>();
List <EdiStatus> _EdiStatusList = _EdiStatusIList as List <EdiStatus>;
return(_EdiStatusList);
}
private List <WebApi.DataModel.CustomModel.SAP.BpArea> GetBpAreaList()
{
string _Sql = @"select * from sapabap1.T005U where land1='CN' and (SPRAS='1' or SPRAS='M' or SPRAS='E' ) and MANDT='888' ";
DataTable dt = OdbcHelper.GetDataTableText(_Sql, null);
string _SqlUS = @"select * from sapabap1.T005U where land1='US' and (SPRAS='1' or SPRAS='M' or SPRAS='E') and MANDT='888' ";
DataTable dtUS = OdbcHelper.GetDataTableText(_SqlUS, null);
dt.Merge(dtUS);
IList <WebApi.DataModel.CustomModel.SAP.BpArea> _BpAreaIList = dt.ToList <WebApi.DataModel.CustomModel.SAP.BpArea>();
List <WebApi.DataModel.CustomModel.SAP.BpArea> _BpAreaList = _BpAreaIList as List <WebApi.DataModel.CustomModel.SAP.BpArea>;
return(_BpAreaList);
}
private IList <QuotationTax> GetQuotationTaxList()
{
string _SqlTax = @"select a.kunnr,b.matnr ,a.KUNNR1,a.LAEDA ,
case a.ztax when '1' then '0' when '2' then '5' when '3' then '16' when '4' then '13' when '' then '' end as tax
from sapabap1.zsdt006 a join sapabap1.zsdt007 b on a.zqtno=b.zqtno where a.ZSTATUS='F' and a.ztax <>1 ";
if (txtDate.Text != "")
{
_SqlTax += " and a.LAEDA > " + txtDate.Text;
}
DataTable dtTax = OdbcHelper.GetDataTableText(_SqlTax, null);
IList <QuotationTax> _TaxList = dtTax.ToList <QuotationTax>();
return(_TaxList);
}
private List <WebApi.DataModel.CustomModel.SAP.BpEmail> GetBpEmailList()
{
BLL.BpEmail _BpEmail = new BLL.BpEmail();
List <WebApi.DataModel.CustomModel.SAP.BpEmail> _BpEmailList = new List <WebApi.DataModel.CustomModel.SAP.BpEmail>();
//抓全部資料，由於不是參數化查詢，第二個參數傳null
string _SqlBUT000 = @"select partner,name_org1 from sapabap1.BUT000 ";
DataTable dtBUT000 = OdbcHelper.GetDataTableText(_SqlBUT000, null);
string _SqlBpEmail = @" SELECT partner1 , bu_sort1,name_first, smtp_addr
FROM sapabap1.BUT051 a
join sapabap1.BUT000 b on a.partner2=b.partner
join sapabap1.ADR6 c on b.persnumber=c.persnumber ";
DataTable dtBpEmail = OdbcHelper.GetDataTableText(_SqlBpEmail, null);
_BpEmailList = _BpEmail.Parse(dtBUT000, dtBpEmail);
return(_BpEmailList);
}
private List <WebApi.DataModel.CustomModel.SAP.BpPriceGroup> GetBpPriceGroupList()
{
List <WebApi.DataModel.CustomModel.SAP.BpPriceGroup> _BpPriceGroupList = new List <WebApi.DataModel.CustomModel.SAP.BpPriceGroup>();
BLL.BpPriceGroup _BpPriceGroup = new BLL.BpPriceGroup();
string _Sql = @" select a.kunnr,c.BU_SORT1,c.BU_SORT2,vkorg,case KONDA when '01' then 'DIS'
when '02' then 'ITP'
when '03' then 'ICAT'
when '04' then 'MSRP'
end as PriceGroup ,b.BAHNE from sapabap1.KNVV a
join sapabap1.kna1 b on a.kunnr=b.kunnr
join sapabap1.but000 c on a.kunnr = c.partner
where c.bu_group='1000'
and a.loevm <> 'X' ";
DataTable dt = OdbcHelper.GetDataTableText(_Sql, null);
_BpPriceGroupList = _BpPriceGroup.Parse(dt);
return(_BpPriceGroupList);
}
/// <summary>
/// get 20181001 to 20191031 all less mog order
/// TWD/JPY need to multiply 100
/// cost = sap price / per
/// </summary>
/// <param name="tag"></param>
/// <returns></returns>
private List <WebApi.DataModel.CustomModel.SAP.SapLessMoqOrder> GetSapLessMoqOrderList(string tag)
{
List <WebApi.DataModel.CustomModel.SAP.SapLessMoqOrder> _SapLessMoqOrderList = new List <WebApi.DataModel.CustomModel.SAP.SapLessMoqOrder>();
BLL.SapMOQ _SapMOQ = new BLL.SapMOQ();
string _SqlOrder = @" select case a.waerk
when 'TWD' then b.netpr/b.kpein*100
when 'JPY' then b.netpr/b.kpein*100
else b.netpr/b.kpein
end as cost,
e.name1,
a.AUART,
b.kpein,a.vbeln,b.KWMENG,b.ZMENG,b.NETPR, a.kunnr,a.erdat,a.waerk,a.spart,
d.sname,
b.matnr,
b.werks from sapabap1.vbak a
join sapabap1.vbap b on a.vbeln=b.vbeln
join sapabap1.vbpa c on a.vbeln=c.vbeln
join sapabap1.pa0001 d on c.PERNR = d.PERNR
join sapabap1.kna1 e on a.kunnr=e.kunnr
where a.AUART not in ('ZDE1','ZDE2','ZCR1' ,'ZCR2', 'ZCR3', 'ZOR8') and
c.parvw='VE'
and a.ERDAT >'20181001' and a.ERDAT <'20191031'
order by a.vbeln ";
DataTable dtOrder = OdbcHelper.GetDataTableText(_SqlOrder, null);
int _A = dtOrder.Rows.Count;
string _SqlMOQ_MARC = @" select BSTMI,WERKS,MATNR from sapabap1.marc
order by MATNR";
string _SqlMOQ_EINE = @"select a.MINBM ,a.NORBM,b.MATNR,WERKS from sapabap1.EINE a
join sapabap1.EINA b on a.INFNR = b.INFNR order by a.NORBM desc ";
string _SqlMOQ = tag == "MARC" ? _SqlMOQ_MARC : _SqlMOQ_EINE;
DataTable dtMOQ = OdbcHelper.GetDataTableText(_SqlMOQ, null);
int _B = dtMOQ.Rows.Count;
string _SqlDepartment = @"SELECT SPRAS,VTEXT ,SPART
FROM sapabap1.TSPAT
WHERE SPRAS = 'M' ";
DataTable dtDepartment = OdbcHelper.GetDataTableText(_SqlDepartment, null);
int _C = dtDepartment.Rows.Count;
_SapLessMoqOrderList = _SapMOQ.Parse(dtOrder, dtMOQ, dtDepartment, tag);
return(_SapLessMoqOrderList);
}
/// <summary>
/// if you join VBEP ,you get repeat data
/// test -- and txt04='S'
/// </summary>
/// <param name="orderNumber"></param>
/// <returns></returns>
private List <SOrder> GetSapSalesHeaderList(string orderNumber)
{
string _Sql = @"select
case a.waerk
when 'TWD' then b.netpr/b.kpein*100
when 'JPY' then b.netpr/b.kpein*100
else b.netpr/b.kpein
end as price,
a.vbeln,b.KWMENG,b.ZMENG,b.NETPR, a.kunnr,a.erdat,a.waerk,a.spart,
b.matnr,
b.werks,
b.ABGRU,
h.EDATU,
h.LIFSP,
f.TXT04,
b.POSNR,
b.LFSTA
from sapabap1.vbak a
join sapabap1.vbap b on a.vbeln=b.vbeln
join sapabap1.kna1 e on a.kunnr=e.kunnr
join sapabap1.JEST g on g.objnr=a.objnr
join sapabap1.TJ30T f on f.STSMA ='Z0000001' and f.estat=g.stat
join sapabap1.VBEP h on a.vbeln=h.vbeln and b.posnr=h.posnr ";
if (_EdiMode)
{
_Sql += " and txt04='S' ";
}
_Sql += " where a.vbeln='" + orderNumber + "'" +
" order by a.vbeln ,b.posnr ";
DataTable dt = OdbcHelper.GetDataTableText(_Sql, null);
IList <SOrder> _SOrderIList = dt.ToList <SOrder>();
List <SOrder> _SOrderList = _SOrderIList as List <SOrder>;
return(_SOrderList);
}
private void updateZSDT046(string orderNumber)
{
string _Date = DateTime.Now.ToString("yyyyMMdd");
string _SqlTax = @"update ZSDT046 set SAP_PROC='S' ,LAEDA='" + _Date + "',AENAM='Edi' where vbeln=" + orderNumber;
DataTable dtTax = OdbcHelper.GetDataTableText(_SqlTax, null);
}
private void insertZSDT046(string orderNumber)
{
string _Date = DateTime.Now.ToString("yyyyMMdd");
string _SqlTax = @"insert into ZSDT046(MANDT,VBELN,SAP_PROC,SEQ,LAEDA,AENAM) values('888','" + orderNumber + "','','','" + _Date + "','Edi') ";
DataTable dtTax = OdbcHelper.GetDataTableText(_SqlTax, null);
}