public DataTable getLoginByAccount(string account, string pwd)
{
string sql = @"SELECT
u.id,
u.UserName,
u.deptID,
u.marsk,
d.DeptName,
d.DeptNumber
FROM
mesusers u
LEFT JOIN mesdepts d ON d.DeptNumber = u.deptID
WHERE
u.account = '" + account + @"'
AND u.PASSWORD = '******'";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(dt);
}
public int writePMToData(DataTable dt)
{
string sqlValue = "";
// devUUID, devNumber, buyDate, devName, devMode, userDept, userDate, userName, mark
for (int i = 0; i < dt.Rows.Count; i++)
{
sqlValue = sqlValue +
"(\"" + dt.Rows[i]["devUUID"].ToString() + "\",\""
+ dt.Rows[i]["devNumber"].ToString() + "\",\""
+ dt.Rows[i]["buyDate"].ToString() + "\",\""
+ dt.Rows[i]["devName"].ToString() + "\",\""
+ dt.Rows[i]["devMode"].ToString() + "\",\""
+ dt.Rows[i]["userDept"].ToString() + "\",\""
+ dt.Rows[i]["userDate"].ToString() + "\",\""
+ dt.Rows[i]["userName"].ToString() + "\",\""
+ dt.Rows[i]["mark"].ToString() + "\" ),";
}
sqlValue = sqlValue.Substring(0, sqlValue.Length - 1) + ";";
string sqlstr = @"INSERT INTO pdamanager (devUUID, devNumber, buyDate, devName, devMode, userDept, userDate, userName, mark ) VALUES " + sqlValue;
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
return(result);
}
public DataTable getLocation(string org)
{
string sql = @"SELECT
tagLocation
FROM
mesworktagscans
WHERE
tagOrg = 'SAA'
AND tagLocation != ''
AND tagLocation IS NOT NULL
GROUP BY
tagLocation" ;
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(dt);
}
public DataTable getSubinv(string org, int searchType)
{
string sql = "";
if (searchType == 1)
{
sql = "SELECT subinv from location WHERE org ='" + org + @"' and subinv like '%HD%' GROUP BY subinv";
}
else
{
sql = "SELECT subinv from location WHERE org ='" + org + @"' and subinv not like '%HD%' GROUP BY subinv";
}
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(dt);
}
public DataTable getFsgTagNumber( )
{
string sql = @"SELECT
tagnumber
FROM
(
SELECT
b1.tagnumber,
b1.con_no,
ROUND( c.con_no, 2 ),
b1.location,
b1.cust_id,
c.po,
c.buyer_item,
c.color_code,
c.size1,
c.qty,
b1.kg,
b1.subinv,
b1.lastscantime,
b2.completed_no,
IF
( isnull( b2.qty ), IF ( isnull( b2.completed_no ), NULL, c.qty ), b2.qty ),
c.gtn_po,
c.corg,
c.pprfno,
b1.updatedate,
c.main_line AS po_line
FROM
tag_stock_info b1
LEFT JOIN con_no c ON b1.cust_id = c.cust_id
AND b1.con_no = c.serial_from
LEFT JOIN tag_inf b2 ON b1.tagnumber = b2.tagnumber
AND c.buyer_item = b2.buyer_item
AND c.color_code = b2.color_code
AND c.size1 = b2.size1
WHERE
1 = 1
AND b1.org = 'TOP'
AND c.po IS NULL
AND b1.cust_id = 'NIKE'
AND b1.lastScanTime > '2020-12-31'
) a
GROUP BY
tagnumber" ;
// 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 result;
}
public int delDoubleRows()
{
string sql = @"
DELETE
FROM
nikeconnect
WHERE
id IN (
SELECT
a.id
FROM
(
SELECT
id
FROM
nikeconnect a
WHERE
( a.PONumber, a.TradingCompanyPO, a.POItem, a.OGACDate, a.Plant ) IN (
SELECT
PONumber,
TradingCompanyPO,
POItem,
OGACDate,
Plant
FROM
nikeconnect
GROUP BY
PONumber,
TradingCompanyPO,
POItem,
OGACDate,
Plant
HAVING
count(*) > 1
)
) a
)
AND ID NOT IN (
SELECT
b.id
FROM
( SELECT max( ID ) id FROM nikeconnect GROUP BY PONumber, TradingCompanyPO, POItem, OGACDate, Plant HAVING count(*)> 1 ) b
)" ;
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
}
return(result);
}
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);
}