public int insetNikeDataToFsgConDetail(DataTable dt)
{
string values = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
values = values + "('" + dt.Rows[i]["id"] + "'," +
"'" + dt.Rows[i]["Cust_id"] + "'," +
"'" + dt.Rows[i]["Serial_From"] + "'," +
"'" + dt.Rows[i]["Buyer_Item"] + "'," +
"'" + dt.Rows[i]["Item_desc"] + "'," +
"'" + dt.Rows[i]["color_code"] + "'," +
"'" + dt.Rows[i]["Size1"] + "'," +
"'" + dt.Rows[i]["con_Qty"] + "'," +
"'" + dt.Rows[i]["qty"] + "'," +
"'" + dt.Rows[i]["pprfno"] + "'),";
}
values = values.Substring(0, values.Length - 1);
string sql = @"INSERT INTO con_detail (
id,
Cust_id,
Serial_From,
Buyer_Item,
Item_desc,
color_code,
Size1,
con_Qty,
qty,
pprfno
)
VALUES
" + values;
// int result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
sql = sql + @" ON DUPLICATE KEY UPDATE id=VALUES(id), Cust_id=VALUES(Cust_id),Serial_From=VALUES(Serial_From) ,Buyer_Item=VALUES(Buyer_Item)
,Item_desc=VALUES(Item_desc),color_code=VALUES(color_code) ,Size1=VALUES(Size1) ,con_Qty=VALUES(con_Qty) ,qty=VALUES(qty)
,pprfno=VALUES(pprfno) " ;
int result = 0;
if (MiddleWare != "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
}
return(result);
}
public DataTable getTagInvoiceById(string Mid)
{
string sql = @"
SELECT
concat_ws(
'-',
ifNULL( s.tagScanPDAUUID, '0' ),
concat(
ifNULL((
SELECT
CASE
LENGTH( tagScanDeptID )
WHEN 1 THEN
concat( '0', tagScanDeptID )
ELSE
tagScanDeptID
END AS tagScanDeptID
FROM
mesworktagscanreceipts
WHERE
id =" + Mid + @"
),
'0'
),
ifNULL( DATE_FORMAT( s.tagScanDateTime, '%Y%m%d' ), '0' )
),
ifNULL( `Version`, '0' )
) AS tagInvoice,
s.tagScanDeptID,
s.Version,
s.tagScanAccount,
s.tagScanDateTime
FROM
mesworktagscanreceipts s
WHERE
s.id =" + Mid;
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public DataTable gettagLocations(string taginvoice, bool isAuto)
{
string sql = "";
if (isAuto)
{
sql = @"SELECT
CASE
WHEN
tagLocation = '' THEN
'%' ELSE tagLocation
END tagLocation
FROM
mesworktagscans
WHERE
tagInvoice LIKE '" + taginvoice + @"%'
GROUP BY
tagLocation" ;
}
else
{
sql = @"SELECT
CASE
WHEN
tagLocation = '' THEN
'%' ELSE tagLocation
END tagLocation
FROM
mesworktagscans
WHERE
tagInvoice LIKE '" + taginvoice + @"%'
GROUP BY
tagLocation" ;
}
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public int UpdataTnfDataToFsgConDetail(DataTable dt)
{
string wherstr = "";
string columnstr = "";
string ids = "";
// SELECT id,Cust_id,Serial_From,Buyer_Item,Item_desc,color_code,Size1,con_Qty,qty,pprfno from con_detail WHERE PPrfNo ='79795851196'
foreach (DataRow row in dt.Rows)
{
ids = ids + "'" + row["id"].ToString() + "',";
}
ids = ids.Substring(0, ids.Length - 1);
for (int j = 1; j < dt.Columns.Count; j++)
{
if (dt.Columns[j].ColumnName == "con_Qty")
{
for (int i = 0; i < dt.Rows.Count; i++)
{
wherstr = wherstr + " WHEN '" + dt.Rows[i]["id"] + "' THEN " + Convert.ToInt32(dt.Rows[i][j].ToString()) + " ";
}
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
wherstr = wherstr + " WHEN '" + dt.Rows[i]["id"] + "' THEN '" + dt.Rows[i][j].ToString() + "' ";
}
}
columnstr = columnstr + dt.Columns[j].ColumnName + " = CASE id " + wherstr + " END,";
wherstr = "";
}
columnstr = columnstr.Substring(0, columnstr.Length - 1);
string sql = @"UPDATE con_detail SET " + columnstr + " WHERE id IN(" + ids + ");";
// int result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
}
return(result);
}
public int uploadCon_detailToMysql(DataTable dt)
{
string value = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
value = value + " ('" + dt.Rows[i]["id"].ToString() + "' , " +
"'" + dt.Rows[i]["Cust_id"].ToString() + "' , " +
"'" + dt.Rows[i]["Serial_From"].ToString() + "' , " +
"'" + dt.Rows[i]["Buyer_Item"].ToString() + "' , " +
"'" + dt.Rows[i]["Item_desc"].ToString() + "' , " +
"'" + dt.Rows[i]["color_code"].ToString() + "' , " +
"'" + dt.Rows[i]["Size1"].ToString() + "' , " +
"'" + dt.Rows[i]["con_Qty"].ToString() + "' , " +
"'" + dt.Rows[i]["qty"].ToString() + "' , " +
"'" + dt.Rows[i]["pprfno"].ToString() + "' " +
" ),";
}
value = value.Substring(0, value.Length - 1);
string sql = @"INSERT INTO `fsg`.`con_detail`
(`id`, `Cust_id`, `Serial_From`, `Buyer_Item`, `Item_desc`, `color_code`,`Size1`, `con_Qty`, `qty`, `pprfno`)
VALUES " + value;
string valueD = @" ON DUPLICATE KEY UPDATE
`id` = VALUES(id),
`Cust_id` = VALUES(Cust_id),
`Serial_From` = VALUES(Serial_From),
`Buyer_Item` = VALUES(Buyer_Item),
`Item_desc` = VALUES(Item_desc),
`color_code` = VALUES(color_code),
`Size1` = VALUES(Size1),
`con_Qty` = VALUES(con_Qty),
`qty` = VALUES(qty),
`pprfno` = VALUES(pprfno) " ;
sql = sql + valueD + ";";
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
}
return(result);
}
public int isHaveByNikeConnect(string Npo)
{
string sql = @"SELECT PONumber from nikeconnect WHERE PONumber ='" + Npo + "'";
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result.Rows.Count);
}
public int isHaveByTradingComanyPO(string Tpo)
{
string sql = @"SELECT GTN_PO FROM `gtn_po` WHERE GTN_PO ='" + Tpo + "'";
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result.Rows.Count);
}
public DataTable getOrg()
{
string sqlstr = @"SELECT DISTINCT org,id FROM location GROUP BY org";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sqlstr);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sqlstr);
}
return(dt);
}
public DataTable getOrgs()
{
string sql = @"SELECT org from inv GROUP BY org;";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(dt);
}
public DataTable getLocations(string org, string subinv)
{
string sqlstr = @"SELECT DISTINCT location,id FROM location WHERE org ='" + org + "' and subinv = '" + subinv + "' AND LOCATION LIKE 'CF%D' GROUP BY location";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sqlstr);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sqlstr);
}
return(dt);
}
public DataTable getFromWriteExcel(string createPC, string starTime, string stopTime)
{
string sql = @"SELECT
d.lineName,
d.DeliveryDate,
d.styleId,
d.colorId,
CASE
WHEN t.AFTER != NULL THEN
t.AFTER ELSE d.sizeName
END sizeName,
sum( d.Qty ) Qty
FROM
delivertb d
LEFT JOIN transize t ON d.sizeName = t.AFTER
OR d.sizeName = t.BEFORE
WHERE
Create_Pc = '" + createPC + @"'
AND isdel = 0
AND deliveryDate BETWEEN STR_TO_DATE('" + starTime + @"','%Y-%m-%d')
AND STR_TO_DATE('" + stopTime + @"','%Y-%m-%d' )
AND isDel =0
GROUP BY
lineName,
DeliveryDate,
styleId,
colorId,
sizeName
ORDER BY
d.deliveryDate,
d.lineName,
d.styleId,
d.colorId,
sizeName;" ;
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(dt);
}
public int UpdataNikeDataToFsgConDetail(DataTable dt)
{
string wherstr = "";
string columnstr = "";
string ids = "";
foreach (DataRow row in dt.Rows)
{
ids = ids + "'" + row["id"].ToString() + "',";
}
ids = ids.Substring(0, ids.Length - 1);
for (int j = 1; j < dt.Columns.Count; j++)
{
if (dt.Columns[j].ColumnName == "con_Qty")
{
for (int i = 0; i < dt.Rows.Count; i++)
{
wherstr = wherstr + " WHEN '" + dt.Rows[i]["id"] + "' THEN " + Convert.ToInt32(dt.Rows[i][j].ToString()) + " ";
}
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
wherstr = wherstr + " WHEN '" + dt.Rows[i]["id"] + "' THEN '" + dt.Rows[i][j].ToString() + "' ";
}
}
columnstr = columnstr + dt.Columns[j].ColumnName + " = CASE id " + wherstr + " END,";
wherstr = "";
}
columnstr = columnstr.Substring(0, columnstr.Length - 1);
string sql = @"UPDATE con_detail SET " + columnstr + " WHERE id IN(" + ids + ");";
// int result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
}
return(result);
}
public DataTable getLocationsBysubinv(string org, string subinv)
{
string sql = @"select location from location where org = '" + org + "' and subinv = '" + subinv + "' group by location;";
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public DataTable updataMesworktagscansByinvoiceIsprint(string id)
{
string sql = @" UPDATE mesworktagscans set isPrints = 1 WHERE ID = '" + id + "'";
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public DataTable getLocation(string subinv)
{
string sql = @"SELECT DISTINCT Location FROM location WHERE subinv='" + subinv + "'";
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public DataTable getLocationByOrgAndSubinv(string org, string subinv)
{
string sql = @"SELECT DISTINCT location from Location WHERE org='" + org + "' AND Subinv = '" + subinv + "'";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(dt);
}
public DataTable getSubinvs(string org)
{
string sqlstr = @"SELECT DISTINCT subinv,id FROM location WHERE org ='" + org + "' and subinv like '%HD' GROUP BY subinv";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sqlstr);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sqlstr);
}
return(dt);
}
public DataTable getSubinvByOrg(string org)
{
string sql = @"SELECT DISTINCT subinv from Location WHERE org='" + org + "'";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(dt);
}
public DataTable getSubinbsByOrg(string org)
{
string sql = @"select subinv from location where org = '" + org + "' group by subinv;";
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public DataTable getStyles()
{
string sqlstr = "SELECT Buyer_Item from con_detail GROUP BY Buyer_Item ORDER BY Buyer_Item";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sqlstr);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sqlstr);
}
return(dt);
}
public int updataStyleCounts(int id, int qtyCount)
{
string sqlstr = @"UPDATE countreceis set qtyCount = " + qtyCount + " WHERE ID=" + id;
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
return(result);
}
public int delRowsByID(int id)
{
string sqlstr = @"UPDATE receis set isFull =1 WHERE id =" + id;
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
return(result);
}
public int insetTnfDataToFsgConDetail(DataTable dt)
{
string values = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
values = values + "('" + dt.Rows[i]["id"] + "'," +
"'" + dt.Rows[i]["Cust_id"] + "'," +
"'" + dt.Rows[i]["Serial_From"] + "'," +
"'" + dt.Rows[i]["Buyer_Item"] + "'," +
"'" + dt.Rows[i]["Item_desc"] + "'," +
"'" + dt.Rows[i]["color_code"] + "'," +
"'" + dt.Rows[i]["Size1"] + "'," +
"'" + dt.Rows[i]["con_Qty"] + "'," +
"'" + dt.Rows[i]["qty"] + "'," +
"'" + dt.Rows[i]["pprfno"] + "'),";
}
values = values.Substring(0, values.Length - 1);
string sql = @"INSERT INTO con_detail (
id,
Cust_id,
Serial_From,
Buyer_Item,
Item_desc,
color_code,
Size1,
con_Qty,
qty,
pprfno
)
VALUES
" + values + ";";
// int result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
}
return(result);
}
public DataTable getLocation(string org)
{
string sql = @"SELECT tagLocation FROM meslocation WHERE tagOrg = '"+ org + "' ORDER BY tagLocation";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(dt);
}
public void upTnfMaxId(int maxId)
{
string sql = @"UPDATE tnfmaxid set tnfDataId=" + maxId + " WHERE id=1";
// DataTable result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
// DataTable dt = new DataTable();
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
}
// return result;
}
public DataTable getLocations(string org, string subinv)
{
string sql = @"SELECT DISTINCT location from location WHERE ORG ='" + org + @"' and subinv ='" + subinv + "' AND location LIKE 'CF%D'";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(dt);
}
public int getTnfMaxId()
{
string sql = @"SELECT tnfDataId FROM tnfmaxid";
// DataTable result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
// return dt;
return(Convert.ToInt32(dt.Rows[0][0].ToString()));
}
public DataTable getSubinvs(string org)
{
string sql = @"SELECT DISTINCT subinv FROM location WHERE org='" + org + "'";
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public int addUser(string[] userInfo)
{
if (userInfo[2] == "")
{
return(-2);
}
;
if (userInfo[5] == "")
{
return(-3);
}
;
string sql = @"INSERT INTO mesusers (account,password,UserName,deptID,Marsk)VALUES(@account,@password,@UserName,@deptID,@Marsk);";
int insets = 0;
if (MiddleWare == "1")
{
MyCatParameter[] p =
{
new MyCatParameter("account", userInfo[1]),
new MyCatParameter("password", userInfo[2]),
new MyCatParameter("UserName", userInfo[3]),
new MyCatParameter("Marsk", userInfo[6]),
new MyCatParameter("deptID", userInfo[5])
};
insets = MyCatfsg_SqlHelper.ExecuteNonQuery(sql, p);
}
else
{
MySqlParameter[] p =
{
new MySqlParameter("account", userInfo[1]),
new MySqlParameter("password", userInfo[2]),
new MySqlParameter("UserName", userInfo[3]),
new MySqlParameter("Marsk", userInfo[6]),
new MySqlParameter("deptID", userInfo[5])
};
insets = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql, p);
}
return(insets);
}
public DataTable getCompketedSyncDataErrors()
{
string sql = @"SELECT
s.id,
s.tagOrg,
d.DeptName,
s.tagScanDeptID,
s.tagScanAccount,
s.tagLine,
s.tagStyle,
s.tagColor,
s.tagSize,
s.tagQty,
s.tagLocation,
s.tagNumber,
s.tagScanDateTime,
s.tagUploadDateTime,
CASE s.isInOrOut
WHEN 0 THEN
'入库'
ELSE
'出库'
END as isInOrOut
FROM
mesworktagscans s
LEFT JOIN mesdepts d ON s.tagScanDeptID = d.DeptNumber
WHERE
s.isSyncMesData =0
and s.tagUploadDateTime< date_sub(now(), interval 1 hour)
ORDER BY s.id,s.tagNumber" ;
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(dt);
}