public override void Run()
{
string sql = @"select top 200 amount,current_amount,item_id,sku_id,store_id,sku_id,order_id,order_type from
ItemAmountChanged where ISNULL(isMove,'0')<>'1' ORDER BY lastchanged desc" ;
//string sql = @"select top 200 amount,current_amount,item_id,sku_id,store_id,sku_id,order_id,order_type from
// ItemAmountChanged where order_id='908593952148647920' and id='29' ORDER BY lastchanged desc";
DataTable dataTable = BusinessDbUtil.GetDataTable(sql);
if (dataTable != null && dataTable.Rows.Count > 0)
{
int count = dataTable.Rows.Count;
int num = (count + 100 - 1) / 100;
int num2 = 100;
int num3 = 0;
Thread[] array = new Thread[num];
for (int i = 0; i < count; i += 100)
{
DataRow[] drs = dataTable.AsEnumerable().Take(num2).Skip(i).ToArray <DataRow>();
array[num3] = new Thread(() =>
{
this.setAmountChanged(drs);
});
array[num3].Name = "线程" + i.ToString();
array[num3].Start();
num3++;
num2 += 100;
}
}
}
public void CreateQT_SPKCB()
{
try
{
string arg = string.Empty;
if (!string.IsNullOrWhiteSpace(base.Config.pullbilldatetime))
{
arg = string.Format(" AND CONVERT(BIGINT,spkcb.LastChanged) > {0} ", base.Config.pullbilldatetime);
}
else
{
arg = " AND 1=1";
}
//条码取自条码对照表数据
//string sql = string.Format(@"select CKDM,spkcb.SPDM,TMDZB.SPTM as SKU,1 as SL,CONVERT(BIGINT,spkcb.LastChanged) as LastChanged from spkcb
// inner join TMDZB on spkcb.SPDM+spkcb.GG1DM+spkcb.GG2DM=TMDZB.SPDM+TMDZB.GG1DM+TMDZB.GG2DM
// where CKDM<>''
// AND CKDM ='947' and KWDM='000' and spkcb.SPDM='602695726815' and spkcb.GG1DM='SZHS' and spkcb.GG2DM='1003'
// group by CKDM,TMDZB.SPTM,spkcb.SPDM,spkcb.GG1DM,spkcb.GG2DM,spkcb.LastChanged
// order by LastChanged asc", arg);
string sql = string.Format(@"select CKDM,spkcb.SPDM,TMDZB.SPTM as SKU,sum(spkcb.SL) as SL,CONVERT(BIGINT,spkcb.LastChanged) as LastChanged from spkcb
left join TMDZB on spkcb.SPDM+spkcb.GG1DM+spkcb.GG2DM = TMDZB.SPDM+TMDZB.GG1DM+TMDZB.GG2DM
left join SHANGPIN on TMDZB.SPDM=SHANGPIN.SPDM
where CKDM<>'' and SHANGPIN.BZHU='蜻蜓平台同步' {0}
group by CKDM,TMDZB.SPTM,spkcb.SPDM,spkcb.GG1DM,spkcb.GG2DM,spkcb.LastChanged
order by spkcb.LastChanged asc" , arg);
DataTable dataTable = BusinessDbUtil.GetDataTable(sql);
if (dataTable != null && dataTable.Rows.Count > 0)
{
int count = dataTable.Rows.Count;
int num = (count + 100 - 1) / 100;
int num2 = 100;
int num3 = 0;
Thread[] array = new Thread[num];
List <long> LastChanged = new List <long>();
LastChanged.Add(0);
for (int i = 0; i < count; i += 100)
{
DataRow[] drs = dataTable.AsEnumerable().Take(num2).Skip(i).ToArray <DataRow>();
array[num3] = new Thread(() =>
{
this.sendSPKCBInfo(drs, LastChanged);
});
array[num3].Name = "线程" + i.ToString();
array[num3].Start();
num3++;
num2 += 100;
}
}
}
catch (Exception ex)
{
LogUtil.WriteError(this, ex.Message, ex.Message);
}
}
public override void Run()
{
string _LastChanged = string.Empty;
if (!string.IsNullOrWhiteSpace(Config.pullbilldatetime))
{
_LastChanged = string.Format(" AND CONVERT(BIGINT,t0.LastChanged) > {0} ", Config.pullbilldatetime);
}
string sql = string.Format(@"select t0.BYZD3,t0.qddm,T0.DJBH,'SPTHD' AS TableName,t0.SL,T0.JE,t0.DM1,
T0.DM4,t0.DM2 AS ckCode,t0.RQ AS created ,t0.zdr,t0.YXRQ,t0.BZ from
SPTHD t0 INNER JOIN CANGKU ON CANGKU.CKDM=t0.DM2
where isnull(T0.Is_Move,0)='1' and isnull(T0.isonline,'0')='0' and isnull(t0.SH,0)=1 and t0.SL>0 {0}
order by t0.lastchanged desc", _LastChanged);
//string sql = string.Format(@"select t0.BYZD3,t0.qddm,T0.DJBH,'SPTHD' AS TableName,t0.SL,T0.JE,t0.DM1,
// T0.DM4,t0.DM2 AS ckCode,t0.RQ AS created ,t0.zdr,t0.YXRQ,t0.BZ from
// SPTHD t0 INNER JOIN CANGKU ON CANGKU.CKDM=t0.DM2
// where t0.djbh='JA600000037' and isnull(T0.isonline,'0')='0' and isnull(t0.SH,0)=1 and t0.SL>0 {0}
// order by t0.lastchanged desc", _LastChanged);
DataTable dataTable = BusinessDbUtil.GetDataTable(sql);
if (dataTable != null && dataTable.Rows.Count > 0)
{
int count = dataTable.Rows.Count;
int num = (count + 100 - 1) / 100;
int num2 = 100;
int num3 = 0;
Thread[] array = new Thread[num];
List<long> LastChanged = new List<long>();
LastChanged.Add(0L);
for (int i = 0; i < count; i += 100)
{
DataRow[] drs = dataTable.AsEnumerable().Take(num2).Skip(i).ToArray<DataRow>();
array[num3] = new Thread(() =>
{
this.sendJTSQD_FromERP(drs, LastChanged);
});
array[num3].Name = "线程" + i.ToString();
array[num3].Start();
num3++;
num2 += 100;
}
}
}
/// <summary>
/// 库存变更 零售退货 taobao.retail.ifashion.itemamount.change
/// 没有同步过的单据 is_move -> 0 已经同步过的单据 is_move -> 1 已经上传库存变更 is_move -> 2
/// </summary>
public override void Run()
{
string arg = string.Empty;
if (!string.IsNullOrWhiteSpace(Config.pullbilldatetime))
{
arg = string.Format(" AND CONVERT(BIGINT,LastChanged) > {0} ", Config.pullbilldatetime);
}
//没有同步过的单据 is_move -> 0 已经同步过的单据 is_move -> 1 已经上传库存变更 is_move -> 2
string sql = string.Format(@"select vMBillID as DJBH,vShop as DM1,fMoney as JE ,CONVERT(bigint,LastChanged) as lastchanged from SG_Gathering
where isnull(SG_Gathering.is_move,'0')='1' and SG_Gathering.fQuantity < 0
and isnull(SG_Gathering.isonline,'0')='0' {0} order by lastchanged asc ", arg);
DataTable dataTable = BusinessDbUtil.GetDataTable(sql);
if (dataTable != null && dataTable.Rows.Count > 0)
{
int count = dataTable.Rows.Count;
int num = (count + 100 - 1) / 100;
int num2 = 100;
int num3 = 0;
Thread[] array = new Thread[num];
List <long> LastChanged = new List <long>();
LastChanged.Add(0L);
for (int i = 0; i < count; i += 100)
{
DataRow[] drs = dataTable.AsEnumerable().Take(num2).Skip(i).ToArray <DataRow>();
array[num3] = new Thread(() =>
{
this.sendLSTH_OffPay(drs, LastChanged);
});
array[num3].Name = "线程" + i.ToString();
array[num3].Start();
num3++;
num2 += 100;
}
}
}
public override void Run()
{
string sql = string.Format(@"select store_id,item_id,sku_id from (
select ROW_NUMBER() over(order by store_id desc) row_index ,store_id,
item_id,sku_id from storesku_mid where
not exists(select 1 from storeskulist where cast(storeskulist.store_id as varchar(50))+
cast(storeskulist.item_id as varchar(50))+ cast(storeskulist.sku_id as varchar(50))
= cast(storesku_mid.store_id as varchar(50))+ cast(storesku_mid.item_id as varchar(50))
+ cast(storesku_mid.sku_id as varchar(50))) ) a where row_index>=0 and row_index<=100 " );
//string sql = "select * from storesku_mid where id ='8'";
DataTable dataTable = BusinessDbUtil.GetDataTable(sql);
if (dataTable != null && dataTable.Rows.Count > 0)
{
string app_key = ConfigUtil.App_key;
string app_secret = ConfigUtil.App_secret;
string iposApiUrl = ConfigUtil.IposApiUrl;
ITopClient topClient = new DefaultTopClient(iposApiUrl, app_key, app_secret, "json");
RetailIfashionSkuinfoGetRequest retailIfashionSkuinfoGetRequest = new RetailIfashionSkuinfoGetRequest();
DbOperation dbOperation = new DbOperation(ConfigUtil.ConnectionString);
string strCmd = "select ID,store_id,item_id,sku_id,sku_bar_code,shop_name,seller_nick,item_title,item_pic,item_price,color,size,short_url,current_amount from storeskulist where 1=0";
DataTable dataTable2 = dbOperation.ExecuteQuery(strCmd).Tables[0];
dataTable2.TableName = "storeskulist";
foreach (DataRow dataRow in dataTable.Rows)
{
string accessToken_QT = InvoicesManage.GetAccessToken_QT(dataRow["store_id"].ToString());
retailIfashionSkuinfoGetRequest.SkuId = dataRow["sku_id"].ToString();
retailIfashionSkuinfoGetRequest.ItemId = dataRow["item_id"].ToString();
RetailIfashionSkuinfoGetResponse retailIfashionSkuinfoGetResponse = topClient.Execute <RetailIfashionSkuinfoGetResponse>(retailIfashionSkuinfoGetRequest, accessToken_QT);
if (retailIfashionSkuinfoGetResponse != null && retailIfashionSkuinfoGetResponse.Result.SuccessAndHasValue && retailIfashionSkuinfoGetResponse.Result.Data != null)
{
LogUtil.WriteInfo(this, "SetSearchOnlineGoodsInfoByAccurate : Body 成功记录", retailIfashionSkuinfoGetResponse.Body);
RetailIfashionSkuinfoGetResponse.SkuInfoDomain data = retailIfashionSkuinfoGetResponse.Result.Data;
DataTable dataTable3 = JsonHelper.SetDataTableFromQT <RetailIfashionSkuinfoGetResponse.SkuInfoDomain>(data, "storeskulist");
foreach (DataRow dataRow2 in dataTable3.Rows)
{
string sqlFlag = string.Format(@"select top 1 id from storeskulist where cast(store_id as varchar(50)) ='{0}' and cast(item_id as varchar(50))='{1}' and cast(sku_id as varchar(50)) ='{2}'
", dataRow2["store_id"].ToString(), dataRow2["item_id"].ToString(), dataRow2["sku_id"].ToString());
var Flag = BusinessDbUtil.ExecuteScalar(sqlFlag);
//如果存在那就不能重复插入
if (Flag != null && Flag.ToString() != "")
{
continue;
}
if (dataRow2["item_id"] != null && dataRow2["item_id"].ToString() != "" && dataRow2["color"] != null && dataRow2["color"].ToString() != "" && (dataRow2["size"] != null & dataRow2["size"].ToString() != ""))
{
StringBuilder stringBuilder = new StringBuilder();
//先插入商品档案
string str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM shangpin WHERE SPDM='{0}')
BEGIN
INSERT INTO shangpin(SPDM,SPMC,DWMC,fjsx1,fjsx2,fjsx3,fjsx4,fjsx5,fjsx6,fjsx7,fjsx8,fjsx9,fjsx10,BZHU,BZSJ,SJ1,SJ2,SJ3,SJ4,BZJJ,JJ1,JJ2
,TZSY,BYZD11,BYZD1,BYZD2,BYZD12,BYZD13,BYZD9,BYZD10,JSJM,BYZD4,BYZD5,BYZD3,BZDW,BYZD14,BYZD15)
VALUES('{0}','{1}','未定义','000','000','000','000','000','000','000','000','000','000','蜻蜓平台同步','{2}','{2}','{2}','{2}','{2}','{2}','{2}','{2}'
,0,0,0,2,1,1,0,0,0,'000','000','000',0,GETDATE(),GETDATE())
END ", dataRow2["item_id"].ToString(), dataRow2["item_title"].ToString(), Convert.ToInt32(dataRow2["item_price"]) / 100);
stringBuilder.Append(str + "\n");
//颜色档案
str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM dbo.GUIGE1 WHERE GGMC='{0}')
BEGIN
DECLARE @ID_GUIGE1 INT = 0
SELECT @ID_GUIGE1 = isnull(max(ID_VALUE),0) FROM ID_CODEID WHERE ID_NAME = 'GUIGE1_QT'
IF @ID_GUIGE1 = 0
BEGIN
insert into GUIGE1(GGDM,GGMC,TYBJ) values('QT_' + CAST(@ID_GUIGE1 AS VARCHAR(6)),'{0}',0)
INSERT INTO ID_CODEID (ID_NAME, ID_VALUE) VALUES ('GUIGE1_QT', 1)
END
ELSE
BEGIN
insert into GUIGE1(GGDM,GGMC,TYBJ) values('QT_' + CAST(@ID_GUIGE1 AS VARCHAR(6)),'{0}',0)
UPDATE ID_CODEID SET ID_VALUE = ID_VALUE + 1 WHERE ID_NAME = 'GUIGE1_QT'
END
END " , dataRow2["color"].ToString());
stringBuilder.Append(str + "\n");
//尺码档案
str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM dbo.GUIGE2 WHERE GGMC='{0}')
BEGIN
DECLARE @GGWZ1 INT = 1,@GGWZ2 INT =1
DECLARE @flag INT = 0
DECLARE @ID_GUIGE2 INT = 0
SELECT @ID_GUIGE2 = isnull(max(ID_VALUE),0) FROM ID_CODEID WHERE ID_NAME = 'GUIGE2_QT'
IF @ID_GUIGE2 = 0
BEGIN
WHILE @GGWZ1 < 11
BEGIN
WHILE @GGWZ2 < 11
BEGIN
IF NOT EXISTS(SELECT 1 FROM GUIGE2 WHERE GGWZ1=@GGWZ1 AND GGWZ2=@GGWZ2)
BEGIN
SET @flag = 1
insert into GUIGE2(GGDM,GGMC,TYBJ,GGWZ1,GGWZ2) values('QT_' + CAST(@ID_GUIGE2 AS VARCHAR(6)),'{0}',0,@GGWZ1,@GGWZ2)
BREAK
END
SET @GGWZ2 = @GGWZ2 + 1
END
IF @flag =1
BEGIN
BREAK
END
ELSE
BEGIN
SET @GGWZ1 = @GGWZ1 + 1
SET @GGWZ2 = 1
END
END
INSERT INTO ID_CODEID (ID_NAME, ID_VALUE) VALUES ('GUIGE2_QT', 1)
END
ELSE
BEGIN
WHILE @GGWZ1<11
BEGIN
WHILE @GGWZ2<11
BEGIN
IF NOT EXISTS(SELECT 1 FROM GUIGE2 WHERE GGWZ1=@GGWZ1 AND GGWZ2=@GGWZ2)
BEGIN
SET @flag = 1
insert into GUIGE2(GGDM,GGMC,TYBJ,GGWZ1,GGWZ2) values('QT_' + CAST(@ID_GUIGE2 AS VARCHAR(6)),'{0}',0,@GGWZ1,@GGWZ2)
BREAK
END
SET @GGWZ2 = @GGWZ2 + 1
END
IF @flag =1
BEGIN
BREAK
END
ELSE
BEGIN
SET @GGWZ1 = @GGWZ1 + 1
SET @GGWZ2 = 1
END
END
UPDATE ID_CODEID SET ID_VALUE = ID_VALUE + 1 WHERE ID_NAME = 'GUIGE2_QT'
END
END " , dataRow2["size"].ToString());
stringBuilder.Append(str + "\n");
//插入商品规则1
str = string.Format(@" IF NOT EXISTS(SELECT 1 FROM SPGG1 INNER JOIN dbo.GUIGE1 ON SPGG1.GGDM= GUIGE1.GGDM WHERE SPDM='{0}' AND dbo.GUIGE1.GGMC='{1}')
BEGIN
DECLARE @GGDM_GUIGE1 VARCHAR(10) = ''
SELECT TOP 1 @GGDM_GUIGE1 = GGDM from GUIGE1 WHERE GGMC = '{1}'
IF @GGDM_GUIGE1 <> ''
INSERT INTO SPGG1(SPDM, GGDM, BYZD2, BYZD3) VALUES('{0}', '' + @GGDM_GUIGE1 + '', '000', '1')
END " , dataRow2["item_id"].ToString(), dataRow2["color"].ToString());
stringBuilder.Append(str + "\n");
//插入商品规则2
str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM SPGG2 INNER JOIN dbo.GUIGE2 ON SPGG2.GGDM = GUIGE2.GGDM WHERE SPDM='{0}' AND dbo.GUIGE2.GGMC='{1}')
BEGIN
DECLARE @GGDM_GUIGE2 VARCHAR(10)=''
SELECT TOP 1 @GGDM_GUIGE2=GGDM from GUIGE2 WHERE GGMC='{1}'
IF @GGDM_GUIGE2<>''
INSERT INTO SPGG2(SPDM,GGDM,BYZD3) VALUES('{0}',''+@GGDM_GUIGE2+'','1')
END " , dataRow2["item_id"].ToString(), dataRow2["size"].ToString());
stringBuilder.Append(str + "\n");
// 插入到TMDZB 第一次插入SPTM 针对的是 sku_id
str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM dbo.TMDZB WHERE SPTM='{0}')
BEGIN
DECLARE @GG1DM_TMDZB VARCHAR(50)= ''
DECLARE @GG2DM_TMDZB VARCHAR(50)= ''
SELECT TOP 1 @GG1DM_TMDZB=GGDM from dbo.GUIGE1 WHERE GGMC='{2}'
SELECT TOP 1 @GG2DM_TMDZB=GGDM from dbo.GUIGE2 WHERE GGMC='{3}'
INSERT INTO TMDZB(SPTM,SPDM,GG1DM,GG2DM) VALUES('{0}','{1}',''+@GG1DM_TMDZB+'',''+@GG2DM_TMDZB+'')
END
" , dataRow2["sku_id"].ToString(), dataRow2["item_id"].ToString(), dataRow2["color"].ToString(), dataRow2["size"].ToString());
// 插入到TMDZB 第一次插入SPTM 针对的是 short_url
str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM dbo.TMDZB WHERE SPTM='{0}')
BEGIN
DECLARE @GG1DM_TMDZB VARCHAR(50)= ''
DECLARE @GG2DM_TMDZB VARCHAR(50)= ''
SELECT TOP 1 @GG1DM_TMDZB=GGDM from dbo.GUIGE1 WHERE GGMC='{2}'
SELECT TOP 1 @GG2DM_TMDZB=GGDM from dbo.GUIGE2 WHERE GGMC='{3}'
INSERT INTO TMDZB(SPTM,SPDM,GG1DM,GG2DM) VALUES('{0}','{1}',''+@GG1DM_TMDZB+'',''+@GG2DM_TMDZB+'')
END
" , dataRow["short_url"].ToString(), dataRow["item_id"].ToString(), dataRow["color"].ToString(), dataRow["size"].ToString());
stringBuilder.Append(str + "\n");
stringBuilder.Append(str + "\n");
//插入客户代码
str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM kehu WHERE khdm='{0}')
BEGIN
INSERT INTO KEHU(KHDM,KHMC,LBDM,QDDM,QYDM,YGDM,BYZD2,JGSD,TJSD,ZK,CKDM,XZDM,TZSY,BYZD25)
VALUES('{0}','{0}','000','000','000','000','1','BZSJ','BZSJ',1,'{0}','2','0',getdate()) END " ,
dataRow2["store_id"].ToString());
stringBuilder.Append(str + "\n");
// 插入仓库代码
str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM dbo.CANGKU WHERE CKDM='{0}')
BEGIN
INSERT INTO CANGKU(CKDM,CKMC,QDDM,YGDM,LBDM,QYDM,XZDM,DH2,JGSD,ZK,TJSD,TZSY)
VALUES('{0}','{0}','000','000','000','000','1','1','BZSJ',1,'BZSJ','0') END " ,
dataRow2["store_id"].ToString());
// 插入仓库库位
str = string.Format(@"
IF NOT EXISTS(SELECT 1 FROM dbo.CKKW WHERE CKDM='{0}')
BEGIN
insert into CKKW(CKDM,KWDM,INUSE,INZK,OUTUSE,OUTZK,BYZD2) values('{0}','000','0',1,'0','1',1)
END ",
dataRow2["store_id"].ToString());
stringBuilder.Append(str + "\n");
stringBuilder.Append(str + "\n");
//插入供货商代码
str = string.Format(@"IF NOT EXISTS(SELECT 1 FROM dbo.GONGHUOSHANG WHERE GHSMC='{0}')
BEGIN
DECLARE @ID_GONGHUOSHANG INT = 0
SELECT @ID_GONGHUOSHANG = isnull(max(ID_VALUE),0) FROM ID_CODEID WHERE ID_NAME = 'GONGHUOSHANG_QT'
IF @ID_GONGHUOSHANG = 0
BEGIN
INSERT INTO GONGHUOSHANG(GHSDM,GHSMC,XZDM,QDDM,LBDM,QYDM,YGDM,DH2,JGSD,ZK,FPLX,TZSY,CreateDate)
VALUES('QT_GHSDM' + CAST(@ID_GONGHUOSHANG AS VARCHAR(6)),'{0}','0','000','000','000','000','1','BZSJ','1','000','0',getdate())
INSERT INTO ID_CODEID (ID_NAME, ID_VALUE) VALUES ('GONGHUOSHANG_QT', 1)
END
ELSE
BEGIN
INSERT INTO GONGHUOSHANG(GHSDM,GHSMC,XZDM,QDDM,LBDM,QYDM,YGDM,DH2,JGSD,ZK,FPLX,TZSY,CreateDate)
VALUES('QT_GHSDM' + CAST(@ID_GONGHUOSHANG AS VARCHAR(6)),'{0}','0','000','000','000','000','1','BZSJ','1','000','0',getdate())
UPDATE ID_CODEID SET ID_VALUE = ID_VALUE + 1 WHERE ID_NAME = 'GONGHUOSHANG_QT'
END END " , dataRow2["shop_name"].ToString());
stringBuilder.Append(str + "\n");
//插入SPKCB
//str = string.Format(@"DECLARE @GG1DM VARCHAR(50) = '',@GG2DM VARCHAR(50) =''
// SELECT TOP 1 @GG1DM=GGDM FROM dbo.GUIGE1 WHERE GGMC='{2}'
// SELECT TOP 1 @GG2DM=GGDM FROM dbo.GUIGE2 WHERE GGMC='{3}'
// IF NOT EXISTS(SELECT 1 FROM dbo.SPKCB WHERE SPDM+GG1DM+GG2DM='{1}'+''+@GG1DM+''+''+@GG2DM+'' AND KWDM='000' AND CKDM='{0}')
// BEGIN
// INSERT INTO SPKCB(CKDM,KWDM,SPDM,GG1DM,GG2DM,SL) VALUES('{0}','000','{1}',''+@GG1DM+'',''+@GG2DM+'','{4}')
// END
// ELSE
// BEGIN
// UPDATE SPKCB SET SL = SL + {4} WHERE CKDM = '{0}' AND SPDM='{1}' AND GG1DM = ''+@GG1DM+'' AND GG2DM =''+@GG2DM+'' END ",
// dataRow["store_id"].ToString(), dataRow2["item_id"].ToString(), dataRow2["color"].ToString(), dataRow2["size"].ToString(), dataRow2["current_amount"].ToString());
//stringBuilder.Append(str + "\n");
if (stringBuilder.ToString() != "")
{
try
{
dbOperation.ExecuteNonQuery(stringBuilder.ToString());
}
catch (Exception ex)
{
LogUtil.WriteError(this, "插入商品基础档单 : sql", "sql : " + stringBuilder.ToString() + "错误日志 :" + ex.Message);
}
}
#region //新增库存调整单
if (dataRow2["current_amount"].ToString() != "0")
{
try
{
string TableName = "CKTZD";
string DJBH = "";
var NoticesName = string.Empty;
Dictionary <string, DataTable> dic = new Dictionary <string, DataTable>();
Dictionary <string, DataTable> dicMX = new Dictionary <string, DataTable>();
List <Dictionary <string, DataTable> > BusinessList = new List <Dictionary <string, DataTable> >();
List <YanShouInfo> ListNameInfoFACHU = new List <YanShouInfo>();
List <YanShouInfo> ListNameInfoYANSHOU = new List <YanShouInfo>();
var exists = false;
lock (Onlock)
{
if (!exists)
{
Regulation shopinfo = new Regulation();
shopinfo.DM2 = dataRow2["store_id"].ToString();
shopinfo.SHR = "QT";
shopinfo.DM1 = "999";
shopinfo.RQ = DateTime.Now.ToShortDateString();
shopinfo.YDJH = dataRow2["store_id"].ToString() + dataRow2["item_id"].ToString() + dataRow2["sku_id"].ToString();
shopinfo.BZ = "蜻蜓平台对接-库存初始化";
shopinfo.JE = Math.Ceiling((Convert.ToDouble(dataRow2["item_price"]) / 100) * Convert.ToDouble(dataRow2["current_amount"])).ToString();
shopinfo.SL = dataRow2["current_amount"].ToString();
shopinfo.ZDR = "QT";
dic = DataTableBusiness.SetBusinessDataTable <Regulation>(shopinfo, TableName, "Regulation", TableName, out DJBH);
dicMX = DataTableBusiness.SetEntryOrderDetail_QT_2(DJBH, TableName, dataRow2, dataRow2["store_id"].ToString());
YanShouInfo infoYS = new YanShouInfo();
try
{
infoYS = InvoicesManage.GetYsInfo(DJBH, TableName, "P_API_Oper_CKTZD_SH", "QT");
}
catch (System.Exception ex)
{
LogUtil.WriteError(this, "库存调整单 执行失败P_API_Oper_CKTZD_SH ;DJBH:" + DJBH);
}
ListNameInfoYANSHOU.Add(infoYS);
}
if (dic.Count > 0 || dicMX.Count > 0)
{
if (dic != null && dicMX != null)
{
BusinessList.Add(dic);
BusinessList.Add(dicMX);
}
}
if (BusinessList.Count > 0)
{
var resultList = DataTableBusiness.SavaBusinessData_SqlParameter(BusinessList, ListNameInfoYANSHOU);
if (resultList)
{
sql = string.Format("UPDATE " + TableName + " SET JE=(SELECT SUM(JE) FROM " + TableName + "MX WHERE DJBH='{0}')" +
",SL=(SELECT SUM(SL) FROM " + TableName + "MX WHERE DJBH='{0}')WHERE DJBH='{0}'", DJBH);
BusinessDbUtil.ExecuteNonQuery(sql);
LogUtil.WriteInfo(this, string.Format(@"ERP业务单据{0}创建成功!对应的电商系统的调整单号:{1}保存成功", DJBH, DJBH), string.Format(@"ERP业务单据{0}创建成功!对应的电商系统的调整单号:{1}保存成功", DJBH, DJBH));
}
else
{
LogUtil.WriteError(this, "仓库调整单保存失败");
}
}
else
{
LogUtil.WriteError(this, "仓库调整单保存失败");
}
}
}
catch (Exception ex)
{
LogUtil.WriteError(this, "仓库调整单保存失败" + ex.Message);
}
}
#endregion
}
DataRow dataRow3 = dataTable2.NewRow();
dataRow3.BeginEdit();
foreach (DataColumn dataColumn in dataTable2.Columns)
{
if (dataColumn.ColumnName.ToString() != "ID")
{
dataRow3[dataColumn.ColumnName] = dataRow2[dataColumn.ColumnName];
}
}
dataRow3.EndEdit();
dataTable2.Rows.Add(dataRow3);
}
}
else
{
LogUtil.WriteInfo(this, "SetSearchOnlineGoodsInfoByAccurate : Body", "SetSearchOnlineGoodsInfoByAccurate - Body : " + retailIfashionSkuinfoGetResponse.Body);
}
}
try
{
if (dataTable2.Rows.Count > 0 && dbOperation.SqlBulkCopy(dataTable2, "storeskulist"))
{
LogUtil.WriteInfo(this, "新增成功", "新增商品档案成功");
}
}
catch (Exception ex)
{
LogUtil.WriteError(this, "error:" + ex.Message);
}
}
}
/// <summary>
/// 销售
/// </summary>
/// <param name="order"></param>
/// <returns></returns>
private bool InsertLSXHD(QT_GoodsInfo order)
{
bool result = false;
string KHDM = order.store_id.ToString();
string sql2 = "select top 1 KHDM,QDDM,CKDM,ZK,JGSD from kehu where khdm='" + KHDM + "'";
DataTable dataTable = BusinessDbUtil.GetDataTable(sql2);
string CKDM = dataTable.Rows[0]["CKDM"].ToString();
string text = dataTable.Rows[0]["JGSD"].ToString();
string QDDM = dataTable.Rows[0]["QDDM"].ToString();
string SL = order.amount.ToString();
var DJBH = InvoicesManage.GetNewDJBH("LSXHD", QDDM, CKDM, "");
string arg = "SELECT djbh,ydjh,qddm,dm1,dm2,dm2_1,rq,sl,je,bz,zdr,rq_4,shr,sh,shrq,ygdm,byzd1,ll,byzd12,je_1,isonline FROM LSXHD";
DataTable table = BusinessDbUtil.GetDataTable(string.Format("{0} WHERE djbh='{1}'", arg, order.store_id));
table.TableName = "LSXHD";
table.PrimaryKey = new DataColumn[]
{
table.Columns["djbh"]
};
//取商品信息 包括价格
string sql = string.Format(@"IF EXISTS (select 1 from TMDZB inner join sg_gatherings on TMDZB.SPDM = sg_gatherings.vstyle
and TMDZB.gg1dm=sg_gatherings.vcolor and TMDZB.gg2dm =sg_gatherings.vsize
where TMDZB.SPDM ='{0}' and TMDZB.SPTM='{1}' and sg_gatherings.vmbillid='{2}' )
BEGIN
select TMDZB.SPDM,TMDZB.GG1DM,TMDZB.GG2DM,sg_gatherings.frealprice as BZSJ from TMDZB inner join sg_gatherings on TMDZB.SPDM = sg_gatherings.vstyle
and TMDZB.gg1dm=sg_gatherings.vcolor and TMDZB.gg2dm =sg_gatherings.vsize
where TMDZB.SPDM ='{0}' and TMDZB.SPTM='{1}' and sg_gatherings.vmbillid='{2}'
END
ELSE
BEGIN
select distinct TMDZB.SPDM,TMDZB.GG1DM,TMDZB.GG2DM,SHANGPIN.BZSJ from
TMDZB inner join SHANGPIN on TMDZB.SPDM=SHANGPIN.SPDM
where TMDZB.SPDM='{0}' and TMDZB.SPTM='{1}'
END" , order.item_id, order.sku_id, order.order_id);
DataTable dt_SP = InvoicesManage.ExecuteQuery(sql);
LogUtil.WriteInfo(this, "查询商品价格信息", "查询价格sql" + sql + "数据结果:" + JsonParser.ToJson(dt_SP));
BusinessDbUtil.DoAction(delegate(DirectDbManger dbManager)
{
try
{
if (table.Rows.Count < 1)
{
if (ValidDJUtil.IsExistKHDM(this, order.order_id, order.store_id))
{
string columns = SqlUtil.GetColumns(table);
DataRow dataRow = table.NewRow();
dataRow["djbh"] = DJBH;
dataRow["ydjh"] = order.order_id;
dataRow["qddm"] = QDDM;
dataRow["dm1"] = order.store_id;
dataRow["dm2"] = CKDM;
dataRow["dm2_1"] = "000";
dataRow["rq"] = DateTime.Now.ToShortDateString();
dataRow["bz"] = "蜻蜓平台同步";
dataRow["sl"] = SL;
//商品价格信息需要取自单据创建的时候小票的价格信息
dataRow["je"] = Convert.ToDecimal(dt_SP.Rows[0]["BZSJ"]) * Convert.ToDecimal(order.amount);
dataRow["zdr"] = "QT";
dataRow["rq_4"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
dataRow["ygdm"] = "000";
dataRow["byzd1"] = ValidDJUtil.GetJGXD(this, KHDM, "JGSD");
dataRow["ll"] = "1";
dataRow["byzd12"] = "1";
dataRow["isonline"] = "1";
dataRow["je_1"] = Convert.ToDecimal(dt_SP.Rows[0]["BZSJ"]) * Convert.ToDecimal(order.amount);
table.Rows.Add(dataRow);
string text2 = SqlUtil.ConvertInsert(table, columns, dataRow);
DataTable dataTable2 = BusinessDbUtil.GetDataTable(string.Format("SELECT djbh,mibh,spdm,gg1dm,gg2dm,ckj,dj,sl,zk,bzje,je FROM LSXHDMX WHERE 1=0", new object[0]));
dataTable2.TableName = "LSXHDMX";
dataTable2.PrimaryKey = new DataColumn[]
{
dataTable2.Columns["djbh"],
dataTable2.Columns["mibh"]
};
bool flag = true;
string columns2 = SqlUtil.GetColumns(dataTable2);
DataRow dataRow3 = dataTable2.NewRow();
dataRow3["djbh"] = DJBH;
dataRow3["mibh"] = 0;
dataRow3["spdm"] = order.item_id;
dataRow3["gg1dm"] = dt_SP.Rows[0]["GG1DM"];
dataRow3["gg2dm"] = dt_SP.Rows[0]["GG2DM"];
dataRow3["dj"] = Convert.ToDecimal(dt_SP.Rows[0]["BZSJ"]);
dataRow3["ckj"] = Convert.ToDecimal(dt_SP.Rows[0]["BZSJ"]);
dataRow3["sl"] = order.amount;
dataRow3["zk"] = "1";
dataRow3["je"] = Convert.ToDecimal(dt_SP.Rows[0]["BZSJ"]) * Convert.ToDecimal(order.amount);
dataRow3["bzje"] = Convert.ToDecimal(dt_SP.Rows[0]["BZSJ"]) * Convert.ToDecimal(order.amount);
dataTable2.Rows.Add(dataRow3);
if (flag)
{
DataTable dataTable3 = BusinessDbUtil.GetDataTable(string.Format("SELECT DJBH,mibh,JSFS,JE FROM LSXHDJS WHERE 1=0", new object[0]));
dataTable3.TableName = "LSXHDJS";
dataTable3.PrimaryKey = new DataColumn[]
{
dataTable3.Columns["DJBH"],
dataTable3.Columns["mibh"]
};
DataRow dataRow4 = dataTable3.NewRow();
dataRow4["DJBH"] = DJBH;
dataRow4["mibh"] = 1;
dataRow4["JSFS"] = "999";
dataRow4["JE"] = Convert.ToDecimal(dt_SP.Rows[0]["BZSJ"]) * Convert.ToDecimal(order.amount);
dataTable3.Rows.Add(dataRow4);
IEnumerable <string> enumerable = SqlUtil.Convert(dataTable2);
IEnumerable <string> enumerable2 = SqlUtil.Convert(dataTable3);
bool flag2 = false;
try
{
sql = dbManager.ExecuteBatchQuery(out flag2, text2, enumerable, enumerable2);
InvoicesManage.UpdateBillPrice(DJBH, "LSXHD");
DataTable procedureParameter = dbManager.GetProcedureParameter("P_LSXHDFLAGPROCESS");
dbManager.SetProcedureParameter(procedureParameter, "DJBH", DJBH);
dbManager.SetProcedureParameter(procedureParameter, "USER", "QT");
dbManager.SetProcedureParameter(procedureParameter, "Flag", "1");
dbManager.SetProcedureParameter(procedureParameter, "OpDate", DateTime.Now.ToShortDateString());
DataTable dataTable4 = dbManager.ExecProcedure("P_LSXHDFLAGPROCESS", procedureParameter);
DataTable procedureParameter2 = dbManager.GetProcedureParameter("P_UPDATEPTKC_1");
dbManager.SetProcedureParameter(procedureParameter2, "ckdm", CKDM);
dbManager.SetProcedureParameter(procedureParameter2, "kwdm", "000");
dbManager.SetProcedureParameter(procedureParameter2, "tblName", "LSXHDMP");
dbManager.SetProcedureParameter(procedureParameter2, "djbh", DJBH);
dbManager.SetProcedureParameter(procedureParameter2, "intSlLx", -1);
DataTable dataTable5 = dbManager.ExecProcedure("P_UPDATEPTKC_1", procedureParameter2);
//更新小票日结状态
var lsxhCounts = "select sum(lsxhdmx.sl) as sl from lsxhdmx left join lsxhd on lsxhd.djbh = lsxhdmx.djbh where lsxhd.ydjh='" + order.order_id + "'";
DataTable lsxhCounts_Table = BusinessDbUtil.GetDataTable(lsxhCounts);
var sg_Counts = "select sum(fquantity) as sl from sg_gatherings where vmbillid='" + order.order_id + "'";
DataTable sg_Counts_Table = BusinessDbUtil.GetDataTable(lsxhCounts);
if (sg_Counts_Table != null && sg_Counts_Table.Rows.Count > 0 && lsxhCounts_Table.Rows[0]["sl"] == sg_Counts_Table.Rows[0]["sl"])
{
//小票明细数量和
BusinessDbUtil.GetDataTable("update sg_gathering set btotal= '1' where vmbillid ='" + order.order_id + "'");
}
if (flag2 && dataTable4 != null && dataTable4.Rows.Count > 0 && dataTable5 != null && dataTable5.Rows.Count > 0)
{
LogUtil.WriteInfo(this, "", "单据保存成功,对应单据编号为:" + DJBH);
result = true;
}
else
{
LogUtil.WriteInfo(this, "", string.Concat(new object[]
{
"单据保存失败,对应sql语句为:",
text2,
enumerable,
enumerable2
}));
}
}
catch (System.Exception ex)
{
LogUtil.WriteError(this, "", "单据保存成功,对应单据编号为:" + order.order_id);
}
}
}
}
else
{
LogUtil.WriteInfo(this, "", "单据已存在,对应单据编号为:" + order.order_id);
}
}
catch (Exception ex)
{
LogUtil.WriteError(this, sql, ex, "");
result = false;
}
});
this.UpdatesetAmountChangedState(order);
return(result);
}
/// <summary>
/// 采购退货
/// </summary>
/// <param name="qT_GoodsInfo2"></param>
private void InsertSPTHD(QT_GoodsInfo qT_GoodsInfo2)
{
string text2 = "SPTHD";
string empty = string.Empty;
Dictionary <string, DataTable> dictionary = new Dictionary <string, DataTable>();
Dictionary <string, DataTable> dictionary2 = new Dictionary <string, DataTable>();
List <Dictionary <string, DataTable> > list = new List <Dictionary <string, DataTable> >();
List <YanShouInfo> list2 = new List <YanShouInfo>();
string sql = "select top 1 KHDM,QDDM,CKDM,ZK,JGSD from kehu where khdm ='" + qT_GoodsInfo2.store_id + "'";
DataTable dataTable = BusinessDbUtil.GetDataTable(sql);
string zPSD = dataTable.Rows[0]["KHDM"].ToString();
string dM = dataTable.Rows[0]["CKDM"].ToString();
string text3 = dataTable.Rows[0]["JGSD"].ToString();
string qDDM = dataTable.Rows[0]["QDDM"].ToString();
Purchase purchase = new Purchase();
purchase.QDDM = qDDM;
//获取供货商代码
string sql3 = string.Format(@"select top 1 GHSDM from GONGHUOSHANG where GHSMC in (select top 1 shop_name from storeskulist
where store_id='{0}' and item_id='{1}' and sku_id='{2}')", zPSD, qT_GoodsInfo2.item_id, qT_GoodsInfo2.sku_id);
string dM2 = BusinessDbUtil.ExecuteScalar(sql3).ToString();
purchase.DM1 = dM2;
//添加虚拟总仓
purchase.DM2 = "QT_XNZC";
purchase.DM2_1 = "000";
purchase.LXDJ = "";
//直配的流程
purchase.DM4 = "ZP";
purchase.QYDM = "000";
purchase.BYZD1 = "0";
purchase.FPLX = "3";
purchase.YGDM = "000";
purchase.BYZD3 = "";
purchase.BYZD12 = "1";
purchase.YGDM = "000";
purchase.isonline = "1";
//他们那边的消息是拆分的
purchase.DJBH = InvoicesManage.GetNewDJBH(text2, purchase.QDDM, purchase.DM1, qT_GoodsInfo2.order_id);
purchase.ZPSD = zPSD;
string dJBH = purchase.DJBH;
purchase.SHR = "QT";
purchase.ZDR = "QT";
purchase.BZ = "蜻蜓平台同步";
purchase.RQ = DateTime.Now.ToString("yyyy-MM-dd");
purchase.YDJH = qT_GoodsInfo2.order_id;
YanShouInfo infoYS = new YanShouInfo();
infoYS.DJBH = purchase.DJBH;
infoYS.TableName = text2;
infoYS.User = "******";
infoYS.Procedure = string.Format("P_API_Oper_{0}_SH", text2); //验收
infoYS.BYZD3 = "";
list2.Add(infoYS);
dictionary = DataTableBusiness.SetBusinessDataTable <Purchase>(purchase, text2, "Purchase", text2);
dictionary2 = DataTableBusiness.SetEntryOrderDetail_QT_1(purchase.DJBH, text2, qT_GoodsInfo2, purchase.DM1);
if (dictionary.Count > 0 && dictionary2.Count > 0)
{
list.Add(dictionary);
list.Add(dictionary2);
}
if (list.Count > 0)
{
try
{
bool flag2 = DataTableBusiness.SavaBusinessData_SqlParameter(list, list2);
if (flag2)
{
sql = "update SPTHD set SPTHD.JE = (select SUM(JE) from SPTHDMX where SPTHDMX.DJBH='" + dJBH + "'),SPTHD.SL = (select SUM(SL) from SPTHDMX where SPTHDMX.DJBH='" + dJBH + "') where SPTHD.DJBH='" + dJBH + "'";
BusinessDbUtil.ExecuteNonQuery(sql);
}
this.UpdatesetAmountChangedState(qT_GoodsInfo2);
LogUtil.WriteInfo(this, "success", "SPTHD 创建成功DJBH" + dJBH);
// 生成商店退货单
Dictionary <string, DataTable> dic = new Dictionary <string, DataTable>();
Dictionary <string, DataTable> dicMX = new Dictionary <string, DataTable>();
List <YanShouInfo> ListNameInfoFACHU = new List <YanShouInfo>();
List <YanShouInfo> ListNameInfoYANSHOU = new List <YanShouInfo>();
List <Dictionary <string, DataTable> > BusinessList = new List <Dictionary <string, DataTable> >();
var DJBH = string.Empty;
var NewTableName = string.Empty;
shopinfo ShopInfo = new shopinfo();
ShopInfo.YDJH = dJBH;
ShopInfo.DM1 = qT_GoodsInfo2.store_id;
//添加虚拟总仓
ShopInfo.DM2 = "QT_XNZC";
ShopInfo.RQ = DateTime.Now.ToString("yyyy-MM-dd");
//ShopInfo.DM5 = orderInfo.brandID;
ShopInfo.DM4 = "ZP";
ShopInfo.DM2_1 = "000";
ShopInfo.ZDR = "QT";
ShopInfo.SL = qT_GoodsInfo2.amount.ToString();
ShopInfo.JE = "0";
ShopInfo.BZ = "蜻蜓平台同步";
bool exists = false;
if (!exists)
{
dic = DataTableBusiness.SetBusinessDataTable <shopinfo>(ShopInfo, "SDTHD", "shopinfo", "SDTHD", out DJBH);
ShopInfo.DJBH = DJBH;
dicMX = DataTableBusiness.SetEntryOrderDetail_QT_1(ShopInfo.DJBH, "SDTHD", qT_GoodsInfo2, ShopInfo.DM1);
if (dic.Count > 0 && dicMX.Count > 0)
{
BusinessList.Add(dic);
BusinessList.Add(dicMX);
}
infoYS = new YanShouInfo();
var infoFC = new YanShouInfo();
infoYS = InvoicesManage.GetYsInfo(DJBH, "SDTHD", "P_API_Oper_SDTHD_SH", "QT");
infoFC = InvoicesManage.GetYsInfo(DJBH, "SDTHD", "P_API_Oper_SDTHD_YS", "QT");
ListNameInfoYANSHOU.Add(infoFC);
ListNameInfoYANSHOU.Add(infoYS);
try
{
var resultList = DataTableBusiness.SavaBusinessData_SqlParameter(BusinessList, ListNameInfoYANSHOU);
if (resultList)
{
LogUtil.WriteInfo(this, "商店退货单保存成功", "DJBH:" + DJBH);
sql = "update SDTHD set SDTHD.JE = (select SUM(JE) from SDTHDMX where SDTHDMX.DJBH='" + DJBH + "'),SDTHD.SL = (select SUM(SL) from SDTHDMX where SDTHDMX.DJBH='" + DJBH + "') where SDTHD.DJBH='" + DJBH + "'";
BusinessDbUtil.ExecuteNonQuery(sql);
}
}
catch (System.Exception ex)
{
LogUtil.WriteError(this, "直配流程商店退货单保存失败", "DJBH:" + DJBH + "message:" + ex.Message);
}
}
else
{
//result = JsonHelper.SuccessXmlMsg("failure", "-1", string.Format("{0}保存单据失败,商店退货单据重复！", orderInfo.orderId));
LogUtil.WriteError(this, "直配流程商店退货单保存失败", "");
}
}
catch (Exception ex)
{
LogUtil.WriteError(this, "failure", string.Format("InsertSPTHD 保存单据失败，请检查系统日志！ {0} + BusinessList : {1}", qT_GoodsInfo2.order_id, JsonParser.ToJson(list)));
}
}
else
{
LogUtil.WriteError(this, "failure", string.Format("保存单据失败,无保存数据,请检查系统日志！", new object[0]));
}
}