public List <RouteInfo> GetRtInfoColl()
{
List <RouteInfo> AllRtVec = new List <RouteInfo>();
DataTable ptable = dbOperation.ExecuteQuery("select * from BUSLINE");
try
{
int nameIndex = ptable.Columns.IndexOf("name");
int idIndex = ptable.Columns.IndexOf("id");
int nodeIndex = ptable.Columns.IndexOf("node");
int priceIndex = ptable.Columns.IndexOf("price");
foreach (DataRow rs in ptable.Rows)
{
RouteInfo RtInfo = new RouteInfo();
RtInfo.setPrice(Convert.ToSingle(rs[priceIndex]));
RtInfo.setName(rs[nameIndex].ToString());
RtInfo.setNode(rs[nodeIndex].ToString());
RtInfo.setId(rs[idIndex].ToString());
AllRtVec.Add(RtInfo);
}
}
catch (Exception e)
{
}
return(AllRtVec);
}
public void Run_test()
{
string app_key = ConfigUtil.App_key;
string app_secret = ConfigUtil.App_secret;
string session = ConfigUtil.session;
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 dataTable = dbOperation.ExecuteQuery(strCmd).Tables[0];
dataTable.TableName = "storeskulist";
retailIfashionSkuinfoGetRequest.SkuId = "4374388018263";
retailIfashionSkuinfoGetRequest.ItemId = "601201018038";
RetailIfashionSkuinfoGetResponse retailIfashionSkuinfoGetResponse = topClient.Execute <RetailIfashionSkuinfoGetResponse>(retailIfashionSkuinfoGetRequest, session);
if (retailIfashionSkuinfoGetResponse != null && retailIfashionSkuinfoGetResponse.Result.SuccessAndHasValue && retailIfashionSkuinfoGetResponse.Result.Data != null)
{
RetailIfashionSkuinfoGetResponse.SkuInfoDomain data = retailIfashionSkuinfoGetResponse.Result.Data;
DataTable dataTable2 = JsonHelper.SetDataTableFromQT <RetailIfashionSkuinfoGetResponse.SkuInfoDomain>(data, "storeskulist");
foreach (DataRow dataRow in dataTable2.Rows)
{
DataRow dataRow2 = dataTable.NewRow();
dataRow2.BeginEdit();
foreach (DataColumn dataColumn in dataTable.Columns)
{
if (dataColumn.ColumnName.ToString() != "ID")
{
dataRow2[dataColumn.ColumnName] = dataRow[dataColumn.ColumnName];
}
}
dataRow2.EndEdit();
dataTable.Rows.Add(dataRow2);
}
}
try
{
if (dbOperation.SqlBulkCopy(dataTable, "storeskulist"))
{
LogUtil.WriteInfo(this, "新增成功", "新增商品档案成功");
}
}
catch (Exception ex)
{
LogUtil.WriteError(this, "error:" + ex.Message);
}
}
private double GetAllPrice(String RouteIdStr)
{
String[] RtIdArr = RouteIdStr.Split(';');
double Price = 0;
for (int i = 0; i < RtIdArr.Length; i++)
{
try
{
DbOperation dbOperation = new DbOperation("PublicTrafficConnectionString");
DataTable ptable = dbOperation.ExecuteQuery("Select * from BUSLINE where id='"
+ RtIdArr[i] + "'");
if (ptable.Rows.Count > 0)
{
Price = Price + Convert.ToSingle(ptable.Rows[0][ptable.Columns.IndexOf("Price")]);
}
}
catch (Exception e)
{
}
}
return(Price);
}
// 通过模糊查询得到线路信息的集合，因为可能会查询到几条线路
public String QueryTrffId(String pName, int from, int to)
{
String resultStr = "";
// TrffLnInfo是临时的表，name是临时字段表示路线名，id也是临时字段表示路线的唯一标识
DataTable ptable = dbOperation.ExecuteQuery("select * from BUSLINE where name like '%"
+ pName + "%'");
try
{
//int nameIndex = ptable.Columns.IndexOf("name");
//int idIndex = ptable.Columns.IndexOf("id");
//int EnvIndex = ptable.Columns.IndexOf("Env");
//foreach(DataRow rs in ptable.Rows)
//{
// String Name = rs[nameIndex].ToString();
// String Id = rs[idIndex].ToString();
// String Env = rs[EnvIndex].ToString();
// resultStr = resultStr + Name + "," + Id + "," + Env + "&";
//}
string json = "{{'NUM':{0},'RESULTSET':[{1}]}}";
string lineFormat = "{{'ID':'{0}','NAME':'{1}','EXTENT':'{2}','STATIONS':[{3}],'PRICE':'{4}','COORDS':'{5}','LENGTH':{6}}}";
if (ptable.Rows.Count == 0)
{
resultStr = "{NUM:0,RESULTSET:[]}";
}
else
{
DataTable dt = this.getPageResult(ptable, from, to);
int count = dt.Rows.Count;
string[] lines = new string[count];
for (int i = 0; i < count; i++)
{
DataRow rs = dt.Rows[i];
string lineId = rs["ID"].ToString();
//路径总长
double totalLen = 0.0;
string totalArcs = "";
//查询经过的站点
string[] stations = rs["NODE"].ToString().Split(";".ToCharArray());
string sql = "select * from BUSSTATION B where ID in ('" + string.Join("','", stations) + "') ORDER BY CASE B.ID ";
for (int j = 0; j < stations.Length; j++)
{
sql += " WHEN '" + stations[j] + "' THEN " + (j + 1).ToString();
}
sql += " END ";
DataTable dt2 = dbOperation.ExecuteQuery(sql);
if (dt2.Rows.Count == 0)
{
return("");
}
stations = new string[dt2.Rows.Count];
string stationFormater = "{{'ID':'{0}','NAME':'{1}','COORDS':'Point({2})'}}"; //,'ARCS':[{3}]
for (int j = 0; j < dt2.Rows.Count; j++)
{
//获取两站点间的实际线路
if (j != dt2.Rows.Count - 1)
{
string node_s = dt2.Rows[j]["ID"].ToString();
string node_e = dt2.Rows[j + 1]["ID"].ToString();
sql = "select * from BUSLINECOORDS where STARTID='" + node_s + "'" + " AND ENDID='" + node_e + "' AND STAID='" + lineId + "'";
DataTable dt3 = dbOperation.ExecuteQuery(sql);
if (dt3.Rows.Count != 0)
{
totalArcs += dt3.Rows[0]["COORDS"].ToString() + ";";
totalLen += double.Parse(dt3.Rows[0]["LENGTH"].ToString());
}
dt3 = null;
}
stations[j] = string.Format(stationFormater, dt2.Rows[j]["ID"].ToString(), dt2.Rows[j]["名称"].ToString(), dt2.Rows[j]["COORDS"].ToString().Replace(",", " "));
}
//处理坐标串为WKT格式
totalArcs = totalArcs.Substring(0, totalArcs.Length - 1);
totalArcs = totalArcs.Replace(",", " ");
totalArcs = totalArcs.Replace(";", ",");
totalArcs = "LINESTRING(" + totalArcs + ")";
lines[i] = string.Format(lineFormat, rs["ID"].ToString(), rs["NAME"].ToString(), rs["ENV"].ToString(), string.Join(",", stations), rs["PRICE"].ToString(), totalArcs, totalLen.ToString());
}
resultStr = string.Format(json, ptable.Rows.Count.ToString(), string.Join(",", lines));
}
}
catch (Exception e)
{
resultStr = e.Message;
}
return(resultStr);
//if (resultStr.Trim().Length == 0)
// return "";
//else
// return resultStr.Substring(0, resultStr.Length - 1);
}
public void GetMatrix(List <string> pPntIdVec, String StartId, String EndId)
{
DataTable rsArcStartId = dbOperation.ExecuteQuery("select * from pnref t where id='"
+ StartId + "' and QXName ='" + QXName + "'");
String ArcStartId = "";
String StartIdCoord = "";
// 起始地名点映射后节点的序号
int StartIdIndex = -1;
try
{
int arcIndex = rsArcStartId.Columns.IndexOf("arc");
int nodeindexIndex = rsArcStartId.Columns.IndexOf("nodeindex");
int CoordsIndex = rsArcStartId.Columns.IndexOf("Coords");
if (rsArcStartId.Rows.Count > 0)
{
DataRow rowArcStartId = rsArcStartId.Rows[0];
ArcStartId = rowArcStartId[arcIndex].ToString();
StartIdIndex = Convert.ToInt32(rowArcStartId[nodeindexIndex]);
StartIdCoord = rowArcStartId[CoordsIndex].ToString();
}
}
catch (Exception e)
{
}
DataTable rsArcEndId = dbOperation.ExecuteQuery("select * from pnref t where id='"
+ EndId + "' and QXName ='" + QXName + "'");
String ArcEndId = "";
String EndIdCoord = "";
// 终止地名点映射后节点的序号
int EndIdIndex = -1;
try
{
int arcIndex = rsArcEndId.Columns.IndexOf("arc");
int nodeindexIndex = rsArcEndId.Columns.IndexOf("nodeindex");
int CoordsIndex = rsArcEndId.Columns.IndexOf("Coords");
if (rsArcEndId.Rows.Count > 0)
{
DataRow rowArcEndId = rsArcEndId.Rows[0];
ArcEndId = rowArcEndId[arcIndex].ToString();
EndIdIndex = Convert.ToInt32(rowArcEndId[nodeindexIndex]);
EndIdCoord = rowArcEndId[CoordsIndex].ToString();
}
}
catch (Exception e)
{
}
pPntIdVec.Add("p" + StartId);
pPntIdVec.Add("p" + EndId);
int j;
String node_s_Id, node_e_Id;
DataTable rsNodes = dbOperation.ExecuteQuery("select * from arc_nodes t where QXName ='" + QXName + "'");
try
{
int node_sIndex = rsNodes.Columns.IndexOf("node_s");
int node_eIndex = rsNodes.Columns.IndexOf("node_e");
foreach (DataRow rowNodes in rsNodes.Rows)
{
node_s_Id = rowNodes[node_sIndex].ToString();
for (j = 0; j < pPntIdVec.Count; j++)
{
if (pPntIdVec[j] == node_s_Id)
{
break;
}
}
if (j == pPntIdVec.Count)
{
pPntIdVec.Add(node_s_Id);
}
node_e_Id = rowNodes[node_eIndex].ToString();
for (j = 0; j < pPntIdVec.Count; j++)
{
if (pPntIdVec[j] == node_e_Id)
{
break;
}
}
if (j == pPntIdVec.Count)
{
pPntIdVec.Add(node_e_Id);
}
}
}
catch (Exception e)
{
}
AdMatrix = new int[pPntIdVec.Count, pPntIdVec.Count];
for (int i = 0; i < pPntIdVec.Count; i++)
{
for (j = 0; j < pPntIdVec.Count; j++)
{
if (i == j)
{
AdMatrix[i, j] = 0;
}
else
{
AdMatrix[i, j] = 99999999;
}
}
}
DataTable rsArcs = dbOperation.ExecuteQuery("select * from arc_nodes t where QXName ='" + QXName + "'");
try
{
int ArcIndex = rsNodes.Columns.IndexOf("Arc");
int node_sIndex = rsNodes.Columns.IndexOf("Node_S");
int node_eIndex = rsNodes.Columns.IndexOf("Node_E");
int CoordsIndex = rsNodes.Columns.IndexOf("Coords");
int isdoubleIndex = rsNodes.Columns.IndexOf("isdouble");
int lengthIndex = rsNodes.Columns.IndexOf("length");
foreach (DataRow rowArcs in rsArcs.Rows)
{
String Arcid = rowArcs[ArcIndex].ToString();
String Node_S = rowArcs[node_sIndex].ToString();
String Node_E = rowArcs[node_eIndex].ToString();
String CoordsStr = rowArcs[CoordsIndex].ToString();
int IsDouble = Convert.ToInt32(rowArcs[isdoubleIndex]);
int Arcslength = Convert.ToInt32(rowArcs[lengthIndex]);
if ((ArcStartId == ArcEndId) && (Arcid == ArcStartId))
{
int Startlen = CalLen(CoordsStr, StartIdIndex, StartIdCoord);
int Endlen = CalLen(CoordsStr, EndIdIndex, EndIdCoord);
int Len = Math.Abs(Startlen - Endlen);
if (Startlen < Endlen)
{
AdMatrix[DbToMatrix("p" + StartId, pPntIdVec), DbToMatrix(
"p" + EndId, pPntIdVec)] = Len;
AdMatrix[DbToMatrix(Node_S, pPntIdVec), DbToMatrix("p"
+ EndId, pPntIdVec)] = Startlen;
AdMatrix[DbToMatrix("p" + EndId, pPntIdVec), DbToMatrix(
Node_E, pPntIdVec)] = Arcslength - Endlen;
if (IsDouble > 0)
{
AdMatrix[DbToMatrix("p" + EndId, pPntIdVec), DbToMatrix(
"p" + StartId, pPntIdVec)] = Len;
AdMatrix[DbToMatrix("p" + EndId, pPntIdVec), DbToMatrix(
Node_S, pPntIdVec)] = Startlen;
AdMatrix[DbToMatrix(Node_E, pPntIdVec), DbToMatrix(
"p" + EndId, pPntIdVec)] = Arcslength - Endlen;
}
}
else
{
AdMatrix[DbToMatrix("p" + EndId, pPntIdVec), DbToMatrix(
"p" + StartId, pPntIdVec)] = Len;
AdMatrix[DbToMatrix(Node_S, pPntIdVec), DbToMatrix("p"
+ EndId, pPntIdVec)] = Endlen;
AdMatrix[DbToMatrix("p" + StartId, pPntIdVec), DbToMatrix(
Node_E, pPntIdVec)] = Arcslength - Endlen;
if (IsDouble > 0)
{
AdMatrix[DbToMatrix("p" + StartId, pPntIdVec), DbToMatrix(
"p" + EndId, pPntIdVec)] = Len;
AdMatrix[DbToMatrix("p" + EndId, pPntIdVec), DbToMatrix(
Node_S, pPntIdVec)] = Endlen;
AdMatrix[DbToMatrix(Node_E, pPntIdVec), DbToMatrix(
"p" + StartId, pPntIdVec)] = Arcslength - Endlen;
}
}
continue;
}
else
{
if (ArcStartId == Arcid)
{
int Startlen = CalLen(CoordsStr, StartIdIndex,
StartIdCoord);
AdMatrix[DbToMatrix(Node_S, pPntIdVec), DbToMatrix("p"
+ StartId, pPntIdVec)] = Startlen;
AdMatrix[DbToMatrix("p" + StartId, pPntIdVec), DbToMatrix(
Node_E, pPntIdVec)] = Arcslength - Startlen;
if (IsDouble > 0)
{
AdMatrix[DbToMatrix("p" + StartId, pPntIdVec), DbToMatrix(
Node_S, pPntIdVec)] = Startlen;
AdMatrix[DbToMatrix(Node_E, pPntIdVec), DbToMatrix(
"p" + StartId, pPntIdVec)] = Arcslength - Startlen;
;
}
continue;
}
if (ArcEndId == Arcid)
{
int Endlen = CalLen(CoordsStr, EndIdIndex, EndIdCoord);
AdMatrix[DbToMatrix(Node_S, pPntIdVec), DbToMatrix("p"
+ EndId, pPntIdVec)] = Endlen;
AdMatrix[DbToMatrix("p" + EndId, pPntIdVec), DbToMatrix(
Node_E, pPntIdVec)] = Arcslength - Endlen;
if (IsDouble > 0)
{
AdMatrix[DbToMatrix("p" + EndId, pPntIdVec), DbToMatrix(
Node_S, pPntIdVec)] = Endlen;
AdMatrix[DbToMatrix(Node_E, pPntIdVec), DbToMatrix(
"p" + EndId, pPntIdVec)] = Arcslength - Endlen;
}
continue;
}
}
AdMatrix[DbToMatrix(Node_S, pPntIdVec), DbToMatrix(Node_E,
pPntIdVec)] = Arcslength;
if (IsDouble > 0)
{
AdMatrix[DbToMatrix(Node_E, pPntIdVec), DbToMatrix(Node_S,
pPntIdVec)] = Arcslength;
}
}
}
catch (Exception e)
{
}
}
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);
}
}
}