public int updataByID(DataTable dt)
{
if (dt.Rows.Count <= 0)
{
return(0);
}
string sqlID = "";
string sqlCASE = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
sqlID = sqlID + dt.Rows[i]["id"].ToString() + ",";
sqlCASE = sqlCASE + " "
+ " WHEN " + dt.Rows[i]["id"].ToString() + " THEN "
+ dt.Rows[i]["stylecount"].ToString();
}
sqlID = sqlID.Substring(0, sqlID.Length - 1);
sqlCASE = sqlCASE.Substring(0, sqlCASE.Length - 1);
string sqlstr = @"UPDATE countreceis SET stylecount = CASE id " + sqlCASE + @" END WHERE id IN (" + sqlID + ")";
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
return(result);
}
public int upPrintPropertysByPnumber(string[] pId)
{
if (pId.Length <= 0)
{
return(0);
}
string sqlValue = "";
string sqlstr = "";
string propertyPrintPC = Dns.GetHostName();
for (int i = 0; i < pId.Length; i++)
{
sqlValue = sqlValue + "'" + pId[i].ToString() + "',";
}
sqlValue = sqlValue.Substring(0, sqlValue.Length - 1);
sqlstr = @"UPDATE propertys set propertyPrintTims = propertyPrintTims+1 ,propertyPrintPC ='" + propertyPrintPC + "' WHERE propertyID in (" + sqlValue + ")";
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
return(result);
}
public int delDoubleRows()
{
string sql = @"
DELETE
FROM
GTN_PO
WHERE
id IN (
SELECT
a.id
FROM
(
SELECT
id
FROM
gtn_po a
WHERE
( a.po, a.GTN_PO ) IN ( SELECT po, gtn_po FROM gtn_po GROUP BY po, gtn_po HAVING count(*) > 1 )
) a
)
AND ID NOT IN ( select b.id from ( SELECT max( ID ) id FROM GTN_PO GROUP BY po, gtn_po 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 writeGtnsToDb(DataTable dt)
{
string sqlstr = "";
string sqlValue = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
sqlValue = sqlValue +
"(\"" + dt.Rows[i]["PO"].ToString() + "\",\""
+ dt.Rows[i]["GTN_PO"].ToString() + "\",\""
+ dt.Rows[i]["create_pc"].ToString() + "\",\""
+ dt.Rows[i]["update_date"].ToString() + "\" ),";
}
sqlValue = sqlValue.Substring(0, sqlValue.Length - 1) + ";";
sqlstr = @"INSERT INTO gtn_po ( PO, GTN_PO, create_pc, update_date
) VALUES " + sqlValue;
// int result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
// DataTable dt = new DataTable();
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
return(result);
}
public int updataReceiError(string org, string line, string style, int qtyCount, int styleCount, string mark)
{
string sqlstr = @"INSERT INTO receierror ( org, line, style, qtyCount, styleCount, createDate, mark )
VALUES
(
'" + org + @"',
'" + line + @"',
'" + style + @"',
" + qtyCount + @",
" + styleCount + @",
'" + DateTime.Now.ToString("yyyy-MM-dd") + @"',
'" + mark + "')";
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
return(result);
}
public int insetMesDepts(string[] depts)
{
string sql = @"
INSERT IGNORE INTO mesdepts ( DeptName, DeptNumber, Marsk ) SELECT
DeptName,
DeptNumber,
Marsk
FROM
( SELECT """ + depts[4] + @""" AS DeptName,
""" + depts[5] + @""" AS DeptNumber,
""" + depts[7] + @""" AS Marsk FROM DUAL ) AS q
WHERE
NOT EXISTS(
SELECT
DeptNumber
FROM
mesdepts
WHERE
mesdepts.DeptNumber = q.DeptNumber ) ";
int insets = 0;
if (MiddleWare == "1")
{
insets = MyCatfsg_SqlHelper.ExecuteNonQuery(sql);
}
else
{
insets = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
}
return(insets);
}
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 int delPropertysByPnumber(List <string> propertyIDs, string delnote)
{
if (propertyIDs.Count <= 0)
{
return(0);
}
string sqlValue = "";
string sqlstr = "";
string propertyDelPC = Dns.GetHostName();
string propertyDelDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
for (int i = 0; i < propertyIDs.Count; i++)
{
sqlValue = sqlValue + "'" + propertyIDs[i].ToString() + "',";
}
sqlValue = sqlValue.Substring(0, sqlValue.Length - 1);
sqlstr = @"UPDATE propertys set propertyIsDel = 1 , propertyDelPC = '" + propertyDelPC + "' , propertyDelNote='" + delnote + "',propertyDelDate ='" + propertyDelDate + "' WHERE propertyID in (" + sqlValue + ")";
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
return(result);
}
public int updateReceiToData(DataTable dt)
{
string sqlWHEN = "";
string sqlID = "";
string sqlCASE = "";
for (int i = 1; i < dt.Columns.Count; i++)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
string value = dt.Rows[j][i].ToString();
if (i == 1)
{
sqlID = sqlID + dt.Rows[j]["ID"].ToString() + ",";
}
if (value.Length <= 0)
{
value = "";
}
sqlWHEN = sqlWHEN +
" WHEN " + dt.Rows[j]["ID"].ToString() + " THEN \"" + value + "\"";
}
sqlCASE = sqlCASE +
" " + dt.Columns[i].ToString() + " = CASE id " + sqlWHEN + " END ,";
sqlWHEN = "";
}
sqlCASE = sqlCASE.Substring(0, sqlCASE.Length - 1);
sqlID = sqlID.Substring(0, sqlID.Length - 1);
string sqlstr = @"UPDATE receis SET " + sqlCASE + " WHERE id IN (" + sqlID + ")";
// org, subinv, line, style, color, size, qtyCount, po, boxCount, receiNumber, receiDate, mark, receiInDate, receiInPcName, receiEmp,isFull
/*
* UPDATE recei
* SET org = CASE id
* WHEN 1 THEN 'SAA'
* WHEN 2 THEN 'SAA'
* WHEN 4 THEN 'SAA'
* END,
* subinv = CASE id
* WHEN 1 THEN 'S_HD'
* WHEN 2 THEN 'S_HD'
* WHEN 4 THEN 'S_HD'
* END
* WHERE id IN (1,2,4)
*/
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
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 updataUser(string[] userInfo)
{
string sql = "";
if (userInfo[2] == "")
{
sql = @"UPDATE mesusers
SET account = @account,
UserName = @UserName,
Marsk = @Marsk,
deptID = @deptID
WHERE
ID = @ID;" ;
}
else
{
sql = @"UPDATE mesusers
SET account = @account,
password = @password,
UserName = @UserName,
Marsk = @Marsk,
deptID = @deptID
WHERE
ID = @ID;" ;
}
int updatas = 0;
if (MiddleWare == "1")
{
MyCatParameter[] p =
{
new MyCatParameter("ID", userInfo[0]),
new MyCatParameter("account", userInfo[1]),
new MyCatParameter("password", userInfo[2]),
new MyCatParameter("UserName", userInfo[3]),
new MyCatParameter("deptID", userInfo[5]),
new MyCatParameter("Marsk", userInfo[6])
};
updatas = MyCatfsg_SqlHelper.ExecuteNonQuery(sql, p);
}
else
{
MySqlParameter[] p =
{
new MySqlParameter("ID", userInfo[0]),
new MySqlParameter("account", userInfo[1]),
new MySqlParameter("password", userInfo[2]),
new MySqlParameter("UserName", userInfo[3]),
new MySqlParameter("deptID", userInfo[5]),
new MySqlParameter("Marsk", userInfo[6])
};
updatas = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql, p);
}
return(updatas);
}
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 int UpdataTnfDataToFsgConppr(DataTable dt)
{
/*
* UPDATE recei
* SET org = CASE id
* WHEN 1 THEN 'SAA'
* WHEN 2 THEN 'SAA'
* WHEN 4 THEN 'SAA'
* END,
* subinv = CASE id
* WHEN 1 THEN 'S_HD'
* WHEN 2 THEN 'S_HD'
* WHEN 4 THEN 'S_HD'
* END
* WHERE id IN (1,2,4)
*/
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++)
{
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_ppr 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 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 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 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 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 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 int updatePMToData(DataTable dt)
{
string sqlWHEN = "";
string sqlID = "";
string sqlCASE = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
string value = dt.Rows[j][i].ToString();
if (i == 1)
{
sqlID = sqlID + dt.Rows[j]["ID"].ToString() + ",";
}
if (value.Length <= 0)
{
value = "";
}
sqlWHEN = sqlWHEN +
" WHEN " + dt.Rows[j]["ID"].ToString() + " THEN \"" + value + "\"";
}
sqlCASE = sqlCASE +
" " + dt.Columns[i].ToString() + " = CASE id " + sqlWHEN + " END ,";
sqlWHEN = "";
}
sqlCASE = sqlCASE.Substring(0, sqlCASE.Length - 1);
sqlID = sqlID.Substring(0, sqlID.Length - 1);
string sqlstr = @"UPDATE pdamanager SET " + sqlCASE + " WHERE id IN (" + sqlID + ")";
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
return(result);
}
public int writeReceiToData(DataTable dt)
{
this.isMany();
string sqlValue = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
sqlValue = sqlValue +
"(\"" + dt.Rows[i]["org"].ToString() + "\",\""
+ dt.Rows[i]["subinv"].ToString() + "\",\""
+ dt.Rows[i]["line"].ToString() + "\",\""
+ dt.Rows[i]["style"].ToString() + "\",\""
+ dt.Rows[i]["color"].ToString() + "\",\""
+ dt.Rows[i]["size"].ToString() + "\",\""
+ dt.Rows[i]["qtyCount"].ToString() + "\",\""
+ dt.Rows[i]["po"].ToString() + "\",\""
+ dt.Rows[i]["boxCount"].ToString() + "\",\""
+ dt.Rows[i]["receiNumber"].ToString() + "\",\""
+ dt.Rows[i]["receiDate"].ToString() + "\",\""
+ dt.Rows[i]["receiEmp"].ToString() + "\",\""
+ dt.Rows[i]["mark"].ToString() + "\",\""
+ dt.Rows[i]["receiInDate"].ToString() + "\",\""
+ dt.Rows[i]["receiInPcName"].ToString() + "\",\""
+ 0 + "\" ),";
}
sqlValue = sqlValue.Substring(0, sqlValue.Length - 1) + ";";
string sqlstr = @"INSERT INTO receis (org, subinv, line, style, color, size, qtyCount, po, boxCount, receiNumber, receiDate,receiEmp, mark, receiInDate, receiInPcName,isFull) VALUES " + sqlValue;
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
return(result);
}
public int delStyleCount(string org, string subinv, string line, string style, string size, int delQty)
{
string sqlstr = @"UPDATE countreceis
SET qtyCount = qtyCount - " + delQty + @"
WHERE
org = '" + org + @"'
AND subinv = '" + subinv + @"'
AND line = '" + line + @"'
AND style = '" + style + "'";
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
return(result);
}
public int insetByID(DataTable dt)
{
if (dt.Rows.Count <= 0)
{
return(0);
}
string values = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
values = values + "('" +
dt.Rows[i]["Org"].ToString() + "','" +
dt.Rows[i]["subinv"].ToString() + "','" +
dt.Rows[i]["line"].ToString() + "','" +
dt.Rows[i]["style"].ToString() + "','" +
dt.Rows[i]["stylecount"].ToString() + "'," +
0 + ",'" +
DateTime.Now.ToString("yyyy-MM-dd") + "'," +
0 + "),";
}
values = values.Substring(0, values.Length - 1);
string sqlstr = @"INSERT INTO countreceis ( Org, subinv, line, style, stylecount, qtyCount, receiInDate, STATUS )
VALUES " + values;
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
return(result);
}
public int updataIsPrints(string tagInvoice, string location)
{
string sql = @"
UPDATE mesworktagscans
SET isprints = isprints + 1
WHERE
tagInvoice = '" + tagInvoice + "'" + @"
AND tagLocation = '" + location + @"'
" ;
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
}
return(result);
}
public int delTNF_Hurley(List <string> ids)
{
string id = "";
foreach (string i in ids)
{
id = id + "," + i;
}
id = id.Substring(1);
string sql = @" DELETE FROM inv WHERE ID IN("+ id + ");";
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
}
return(result);
}
public int insertPropertys(DataTable dt)
{
if (dt.Rows.Count <= 0)
{
return(0);
}
//保存前查询是否已有此财编号
string sqlValue = "";
string sqlstr = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
// str=str.Replace("abc","ABC");
sqlValue = sqlValue + "(\"" +
dt.Rows[i]["erpid"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["org"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyID"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyName"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyMode"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyType"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["buyDate"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyDept"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyLocal"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyBuyID"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertySavePerson"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyUnit"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyPrintTims"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyIsDel"].ToString().Replace("\"", "'") + "\"),";
}
sqlValue = sqlValue.Substring(0, sqlValue.Length - 1) + ";";
sqlstr = @"INSERT INTO propertys (
erpid,
org,
propertyID,
propertyName,
propertyMode,
propertyType,
buyDate,
propertyDept,
propertyLocal,
propertyBuyID,
propertySavePerson,
propertyUnit,
propertyPrintTims,
propertyIsDel
) VALUES " + sqlValue;
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
return(result);
}
public int writeCompareFileToDb(DataTable dt)
{
//先删除上次上传的资料 update Create_pc = Dns.HostName and isDel =0 为 isDel =1
string upsql = @"
UPDATE delivertb
SET isDel = 1
WHERE
Create_Pc = '" + Dns.GetHostName() + @"'
AND isDel =0 " ;
if (MiddleWare == "1")
{
MyCatfsg_SqlHelper.ExecuteNonQuery(upsql);
}
else
{
Mysqlfsg_SqlHelper.ExecuteNonQuery(upsql);
}
// 再更新这次上传的资料
string sqlstr = "";
string sqlValue = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
string qtys = dt.Rows[i]["qty"].ToString().Trim();
if (qtys.Length <= 0 || qtys == "0")
{
continue;
}
sqlValue = sqlValue +
"(\"" + dt.Rows[i]["lineName"].ToString().Trim() + "\",\""
+ dt.Rows[i]["deliveryDate"].ToString().Trim() + "\",\""
+ dt.Rows[i]["invoiceNo"].ToString().Trim() + "\",\""
+ dt.Rows[i]["styleId"].ToString().Trim() + "\",\""
+ dt.Rows[i]["gtnPO"].ToString().Trim() + "\",\""
+ dt.Rows[i]["idNoName"].ToString().Trim() + "\",\""
+ dt.Rows[i]["colorId"].ToString().Trim() + "\",\""
+ dt.Rows[i]["sizeName"].ToString().Trim() + "\",\""
+ qtys + "\",\""
+ Dns.GetHostName() + "\",\""
+ 0 + "\",\""
+ DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\" ),";
}
sqlValue = sqlValue.Substring(0, sqlValue.Length - 1) + ";";
sqlstr = @"INSERT INTO delivertb ( lineName, deliveryDate, invoiceNo, styleId, gtnPO, idNoName, colorId, sizeName, qty,create_pc,isDel,createDate ) VALUES " + sqlValue;
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
}
return(result);
}
public int insetTnfDataToFsgConppr(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]["qty"] + "'," +
"'" + dt.Rows[i]["org"] + "'," +
"'" + dt.Rows[i]["PPrfNo"] + "'," +
"'" + dt.Rows[i]["count1"] + "'," +
"'" + dt.Rows[i]["create_pc"] + "'," +
"'" + dt.Rows[i]["update_date"] + "'," +
"'" + dt.Rows[i]["con_no"] + "'," +
"'" + dt.Rows[i]["country_code"] + "'," +
"'" + dt.Rows[i]["con_to"] + "'," +
"'" + dt.Rows[i]["Pkg_Code"] + "'," +
"'" + dt.Rows[i]["Scan_ID"] + "'," +
"'" + dt.Rows[i]["Net_Net"] + "'," +
"'" + dt.Rows[i]["Con_Net"] + "'," +
"'" + dt.Rows[i]["con_Gross"] + "'," +
"'" + dt.Rows[i]["con_l"] + "'," +
"'" + dt.Rows[i]["con_W"] + "'," +
"'" + dt.Rows[i]["con_H"] + "'," +
"'" + dt.Rows[i]["b_Volume"] + "'," +
"'" + dt.Rows[i]["PO"] + "'," +
"'" + dt.Rows[i]["MAIN_LINE"] + "'),";
}
values = values.Substring(0, values.Length - 1);
string sql = @"INSERT INTO con_ppr (
id,
Cust_id,
Serial_From,
qty,
org,
PPrfNo,
count1,
create_pc,
update_date,
con_no,
country_code,
con_to,
Pkg_Code,
Scan_ID,
Net_Net,
Con_Net,
con_Gross,
con_l,
con_W,
con_H,
b_Volume,
PO,
MAIN_LINE
)
VALUES
" + values + ";";
// int result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
// DataTable dt = new DataTable();
int result = 0;
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExecuteNonQuery(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sql);
}
return(result);
}