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 getMesworktagscansByinvoiceDetail(string tagInvoice)
{
string sql = @"
SELECT s.Id,
s.tagNumber ,
s.tagScanDateTime ,
s.tagStyle ,
s.tagColor ,
s.tagSize ,
s.tagQty ,
s.tagOrg ,
d.DeptName ,
s.tagScanAccount
FROM
mesworktagscans s
LEFT JOIN mesdepts d ON d.DeptNumber = s.tagScanDeptID
WHERE
tagInvoice = '" + tagInvoice + @"'
ORDER BY id" ;
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public bool isExistsByaccount(string account)
{
string sql = @"SELECT id FROM mesusers WHERE account='" + account + "'";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
if (dt.Rows.Count <= 0)
{
return(false);
}
else
{
return(true);
}
}
public DataTable getStyleCounts(string org, string outsubinv, string outLine, string style)
{
string sqlstr = @"SELECT
id,
styleCount,
qtyCount
FROM
countreceis
WHERE
org = '" + org + @"'
AND subinv = '" + outsubinv + @"'
AND line = '" + outLine + @"'
AND style = '" + style + @"'
AND STATUS = 0" ;
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sqlstr);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sqlstr);
}
return(dt);
}
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 getPropertysByPnumberFromLocalHost(string propertyIDs)
{
string[] propertyID = propertyIDs.Split('|');
string sql = "";
string wherestr = "";
if (propertyID.Length > 0)
{
for (int i = 0; i < propertyID.Length; i++)
{
//('AE9O-0184','AE9O-0183','AE9O-0189')
wherestr = wherestr + "'" + propertyID[i] + "',";
}
}
wherestr = wherestr.Remove(wherestr.Length - 1, 1);
sql = @"SELECT * from propertys WHERE propertyID in (" + wherestr + ")";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(dt);
}
public List <string> getOrg()
{
List <string> orgs = new List <string>();
string sqlstr = "SELECT org FROM location GROUP BY org";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sqlstr);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sqlstr);
}
if (dt.Rows.Count <= 0)
{
orgs.Add("没有数据");
return(orgs);
}
foreach (DataRow dr in dt.Rows)
{
orgs.Add(dr["org"].ToString().Trim().ToUpper());
}
return(orgs);
}
public DataTable getOffSet(string org, string subinv, string location, string starTime, string stopTime)
{
string sql = @"
SELECT
TagNumber
FROM
invreceis
WHERE
org = '" + org + @"'
AND subinv = '" + subinv + @"'
AND line = '" + location + @"'
AND scantime BETWEEN STR_TO_DATE( '" + starTime + @"', '%Y-%m-%d' )
AND STR_TO_DATE( '" + stopTime + "','%Y-%m-%d' )";
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public DataTable getMoveLocals(string tags)
{
string sql = @"SELECT
id,
TagNumber,
max( ScanTime ) max_time ,
Location
FROM
inv
WHERE
TagNumber = '" + tags + @"'
GROUP BY
ScanTime,
id
ORDER BY
ScanTime DESC
LIMIT 0,
1" ;
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public List <string> getColors(string style)
{
List <string> colors = new List <string>();
string sqlstr = "SELECT color_code from con_detail WHERE Buyer_Item='" + style + "' GROUP BY color_code ORDER BY color_code";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sqlstr);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sqlstr);
}
if (dt.Rows.Count <= 0)
{
colors.Add("没有数据");
return(colors);
}
foreach (DataRow dr in dt.Rows)
{
colors.Add(dr["color_code"].ToString().Trim().ToUpper());
}
return(colors);
}
public List <string> getSubinv(string org)
{
List <string> subinvs = new List <string>();
string sqlstr = "SELECT subinv FROM location WHERE org = '" + org + "' GROUP BY subinv ";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sqlstr);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sqlstr);
}
if (dt.Rows.Count <= 0)
{
subinvs.Add("没有数据");
return(subinvs);
}
foreach (DataRow dr in dt.Rows)
{
subinvs.Add(dr["subinv"].ToString().Trim().ToUpper());
}
return(subinvs);
}
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 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 DataTable getReceiFromNoBarCode(string org, string subinv, string location, string starTime, string stopTime)
{
string sql = @"
SELECT
org,
subinv,
line,
style,
color,
size,
SUM( qtyCount ) qtyCount,
SUM( PO ) OffsetQty,
receiNumber ,
receiDate,
receiInPcName
FROM
receis
WHERE
org = '" + org + @"'
AND subinv = '" + subinv + @"'
AND line = '" + location + @"'
AND qtyCount > 0
AND isFull = 0
AND receiDate BETWEEN '" + starTime + "' and '" + stopTime + @"'
GROUP BY
org,
subinv,
line,
style,
color,
size,
receiNumber,
receiDate,
receiInPcName
ORDER BY
org,
subinv,
line,
receiDate,
style,
color,
size;" ;
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public DataTable getUserInfo(string[] searchParameter)
{
// { account, processID, userName };
string deptID = searchParameter[1];
MyCatParameter[] cp = new MyCatParameter[3];
MySqlParameter[] mp = new MySqlParameter[3];
if (deptID == "-1")
{
deptID = "";
}
if (MiddleWare == "1")
{
cp[0] = new MyCatParameter("account", searchParameter[0]);
cp[1] = new MyCatParameter("deptID", deptID);
cp[2] = new MyCatParameter("userName", searchParameter[2]);
}
else
{
mp[0] = new MySqlParameter("account", searchParameter[0]);
mp[1] = new MySqlParameter("deptID", deptID);
mp[2] = new MySqlParameter("userName", searchParameter[2]);
};
// account ,processID, userName
string sql = @"SELECT
ID,
account,
userName,
deptID,
marsk
FROM
mesusers
WHERE 1 = 1 and
if(@account ='',0=0, account = @account ) and
if(@deptID ='',0=0, deptID = @deptID ) and
if(@userName ='',0=0, userName = @userName )
ORDER BY
Id;" ;
DataTable users = new DataTable();
if (MiddleWare == "1")
{
users = MyCatfsg_SqlHelper.ExcuteTable(sql, cp);
}
else
{
users = Mysqlfsg_SqlHelper.ExcuteTable(sql, mp);
};
return(users);
}
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 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 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 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 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 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 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 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 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 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);
}