public List <NVLTheoLSX> ListNVL(string code, string No)
{
List <NVLTheoLSX> _listNVL = new List <NVLTheoLSX>();
sqlERPCON query = new sqlERPCON();
StringBuilder strSQL = new StringBuilder();
DataTable dt = new DataTable();
strSQL.Append("select TB001,TB002, TB003,TB004,TB005,TB006 from MOCTB where TB006 ='****' and TB018 ='Y' and ");
strSQL.Append(" TB001 = '" + code + "' and ");
strSQL.Append(" TB002 = '" + No + "'");
query.sqlDataAdapterFillDatatable(strSQL.ToString(), ref dt);
//Load data into list
for (int i = 0; i < dt.Rows.Count; i++)
{
NVLTheoLSX nVL = new NVLTheoLSX();
nVL.code = dt.Rows[i]["TB001"].ToString();
nVL.No = dt.Rows[i]["TB002"].ToString();
nVL.NVL_TB003 = dt.Rows[i]["TB003"].ToString();
nVL.NVLCan_TB004 = dt.Rows[i]["TB004"].ToString() != "" ? double.Parse(dt.Rows[i]["TB004"].ToString()):0;
nVL.NVLLanh_TB005 = dt.Rows[i]["TB005"].ToString() != "" ? double.Parse(dt.Rows[i]["TB005"].ToString()) : 0;
nVL.NVLPercentLanh = (nVL.NVLCan_TB004 != 0) ? (nVL.NVLLanh_TB005 / nVL.NVLCan_TB004) :((nVL.NVLCan_TB004 == nVL.NVLLanh_TB005)?1:0);
nVL.CD_TB006 = dt.Rows[i]["TB006"].ToString();
_listNVL.Add(nVL);
}
return(_listNVL);
}
public List <LSX_SFTTA> ListSFTTA(string code, string No)
{
List <LSX_SFTTA> lSX_SFTTAs = new List <LSX_SFTTA>();
sqlERPCON query = new sqlERPCON();
StringBuilder strSQL = new StringBuilder();
DataTable dt = new DataTable();
strSQL.Append("select TA001,TA002,TA003,TA004,TA008,TA009,TA010,TA011,TA012 from SFCTA where TA003 = '0010' and ");
strSQL.Append(" TA001 = '" + code + "' and ");
strSQL.Append(" TA002 = '" + No + "'");
query.sqlDataAdapterFillDatatable(strSQL.ToString(), ref dt);
//Load data into list
for (int i = 0; i < dt.Rows.Count; i++)
{
LSX_SFTTA sFTTA = new LSX_SFTTA();
sFTTA.code = dt.Rows[i]["TA001"].ToString();
sFTTA.No = dt.Rows[i]["TA002"].ToString();
sFTTA.MaSX_TA003 = dt.Rows[i]["TA003"].ToString();
sFTTA.MaSX_TA004 = dt.Rows[i]["TA004"].ToString();
sFTTA.NgayBatdau_TA008 = dt.Rows[i]["TA008"].ToString();
sFTTA.NgayKetThuc_TA009 = dt.Rows[i]["TA009"].ToString();
sFTTA.SLKeHoach_TA010 = dt.Rows[i]["TA010"].ToString() != "" ? double.Parse(dt.Rows[i]["TA010"].ToString()) : 0;
sFTTA.SLOutput_TA011 = dt.Rows[i]["TA011"].ToString() != "" ? double.Parse(dt.Rows[i]["TA011"].ToString()) : 0;
sFTTA.SLBaoPhe_TA012 = dt.Rows[i]["TA012"].ToString() != "" ? double.Parse(dt.Rows[i]["TA012"].ToString()) : 0;
lSX_SFTTAs.Add(sFTTA);
}
return(lSX_SFTTAs);
}
public void updateERP(string barcode, string output, string NG, string date, string time)
{
try
{
string[] QR = barcode.Split(';');
string[] ML = QR[0].Split('-');
sqlERPCON data = new sqlERPCON();
//string TA011 = data.sqlExecuteScalarString("select sum(TC014) from SFCTC where TC004 = '" + ML[0] + "' and TC005 = '" + ML[1] + "' and TC001 = 'D201'", SQLCommon.ERP_TL_V2);
//string TA012 = data.sqlExecuteScalarString("select sum(TC016) from SFCTC where TC004 = '" + ML[0] + "' and TC005 = '" + ML[1] + "' and TC001 = 'D201'", SQLCommon.ERP_TL_V2);
//update SFCTA row B01
StringBuilder updateSFCTA = new StringBuilder();
updateSFCTA.Append("update SFCTA set TA011 = TA011 +" + output + ", TA012 = TA012+ " + NG + ",TA031 ='" + date + "' where TA001 = '" + ML[0] + "' and TA002 = '" + ML[1] + "' and TA003 = '" + QR[1] + "' and TA004 = '" + QR[2] + "'");
sqlERPCON sqlUpdate = new sqlERPCON();
sqlUpdate.sqlExecuteNonQuery(updateSFCTA.ToString(), false);
//update SFCTA row B02
StringBuilder updateSFCTAB02 = new StringBuilder();
updateSFCTAB02.Append("update SFCTA set TA010 = TA010 + " + output + ", MODIFIER ='BQC01', MODI_DATE ='" + date + "',MODI_TIME ='" + time + "', MODI_AP ='SFT', MODI_PRID ='', TA030= '" + date + "' where TA001 = '" + ML[0] + "' and TA002 = '" + ML[1] + "' and TA003 ='0020' and TA004 = 'B02' ");
sqlUpdate.sqlExecuteNonQuery(updateSFCTAB02.ToString(), false);
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "updateERP(string barcode)", ex.Message);
}
}
public string GetTC002()
{
string _TC002 = "";
string dateFormat = DateTime.Now.ToString("yyMM");
string countFormatReset = "0001";
int countUp = 0;
sqlERPCON data = new sqlERPCON();
string strData = data.sqlExecuteScalarString("select max(TB002) from SFCTB where TB001 = 'D201' ");
if (strData != null && strData.Trim().Count() == 8)
{
string DateDatabase = strData.Trim().Substring(0, 4);
string strCount = strData.Trim().Substring(4);
if (dateFormat == DateDatabase)
{
countUp = int.Parse(strCount) + 1;
string countFormatup = countUp.ToString("0000");
_TC002 = dateFormat + countFormatup;
}
else
{
_TC002 = dateFormat + countFormatReset;
}
}
return(_TC002);
}
public static void exportcsvToPLC(string linkListProduct)
{
try
{
string path = linkListProduct;
File.Delete(path + "ListProduct.csv");
StringBuilder sql = new StringBuilder();
sql.Append(@"
select TA001+'-'+RTRIM(TA002)+';'+TA003+';'+TA004+';'+ RTRIM(TA006), RTRIM(TC047),RTRIM(TA010), RTRIM(TA011), RTRIM(TA012) ,TA001,TA002,TA003,TA004, TA006 from SFCTA
left join SFCTC on TA001 = TC004 and TA002 = TC005
where 1=1
and TA004 = 'B01'
and TA011+TA012 <TA010
group by TA001,TA002,TA003,TA004, TA006, TA010, TA011,TA012, TC047
");
DataTable dtshow = new DataTable();
sqlERPCON data = new sqlERPCON();
data.sqlDataAdapterFillDatatable(sql.ToString(), ref dtshow);
StringBuilder builder = new StringBuilder();
int rowcount = dtshow.Rows.Count;
int columncount = dtshow.Columns.Count;
List <string> cols = new List <string>();
// builder.AppendLine(string.Join("\t", cols.ToArray()));
for (int i = 0; i < rowcount; i++)
{
cols = new List <string>();
for (int j = 0; j < 5; j++) //Chỉ lay 4 cọt đâu thôi, yêu cầu của Đức
{
cols.Add(dtshow.Rows[i][j].ToString() + @",");
}
builder.AppendLine(string.Join("", cols.ToArray()));
}
System.IO.File.WriteAllText(path + "ListProduct.csv", builder.ToString());
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "exportcsvToPLC()", ex.Message);
}
}
public void InsertdataToERP(string barcode, string output, string NG, string date, string time)
{
try
{
string[] QR = barcode.Split(';');
string[] ML = QR[0].Split('-');
//string date = DateTime.Now.ToString("yyyyMMdd");
//string time = DateTime.Now.ToString("HH:mm:ss");
// string month = DateTime.Now.ToString("yyMM");
string month = date.Substring(2, 6);
sqlERPCON sqlERPCON = new sqlERPCON();
// An chinh sua
string TC002 = GetTC002();
TO008 = TC002;
TOID = "D201-" + TC002; //chay thuc te
//
string TC047 = sqlERPCON.sqlExecuteScalarString("select distinct TA006 from MOCTA where TA001 = '" + ML[0] + "' and TA002 = '" + ML[1] + "'");
ITEMID_TC047 = TC047;
string TC048 = sqlERPCON.sqlExecuteScalarString("select distinct TA034 from MOCTA where TA001 = '" + ML[0] + "' and TA002 = '" + ML[1] + "'");
ITEMNAME_TC048 = TC048;
SQLSFTTLVN2 sQLSFTTLVN2 = new SQLSFTTLVN2();
ITEMDESCRIPTION = sQLSFTTLVN2.sqlExecuteScalarString("select distinct ITEMDESCRIPTION from SFT_TRANSORDER_LINE where TRANSORDERTYPE = 'D101' and KEYID = '" + QR[0] + "'");
string TA007 = sqlERPCON.sqlExecuteScalarString("select distinct TA007 from SFCTA where TA004 ='B01' and TA001 = '" + ML[0] + "' and TA002 = '" + ML[1] + "'");
int TC036 = int.Parse(output) + int.Parse(NG);
// AN chinh sua
TRANSNO = GetTB039(date); //update to transfer table
sqlERPCON sqlInsert = new sqlERPCON(); //
StringBuilder sqlInsertSFCTC = new StringBuilder();
sqlInsertSFCTC.Append("insert into SFCTC ");
sqlInsertSFCTC.Append(@"(COMPANY,CREATOR,USR_GROUP,CREATE_DATE,MODIFIER,MODI_DATE,FLAG,CREATE_TIME,CREATE_AP,CREATE_PRID,MODI_TIME,MODI_AP,MODI_PRID,");
sqlInsertSFCTC.Append(@"TC001,TC002,TC003,TC004,TC005,TC006,TC007,TC008,TC009,TC010,TC011,TC012,TC013,TC014,TC015,TC016,TC017,TC018,TC019,TC020,");
sqlInsertSFCTC.Append(@"TC021,TC022,TC023,TC024,TC025,TC026,TC027,TC033,TC034,TC035,TC036,TC037,TC038,TC039,TC040,");
sqlInsertSFCTC.Append(@"TC041,TC042,TC043,TC044,TC045,TC046,TC047,TC048,TC049,TC050,TC051,TC055)");
sqlInsertSFCTC.Append(" values ( ");
sqlInsertSFCTC.Append("'TECHLINK','BQC01','JG01','" + date + "','BQC01','" + date + "',2,'" + time + "','SFT','Sftb03','" + time + "','SFT','',");
sqlInsertSFCTC.Append("'D201','" + TC002 + "','0001','" + ML[0] + "','" + ML[1] + "','" + QR[1] + "','" + QR[2] + "','0020','B02','PCS','','','1'," + output + ",0," + NG + ",0,0,0,0,");
sqlInsertSFCTC.Append("0,'Y','B01','',0,'N','N','" + date + "','" + date + "','N'," + TC036 + ",0,'" + date + "','0','',");
sqlInsertSFCTC.Append("'B01',0,0,0,0,0,'" + TC047 + "','" + TC048 + "','" + ITEMDESCRIPTION + "','KG','0','N'"); //update new 21-oct
sqlInsertSFCTC.Append(")");
sqlInsert.sqlExecuteNonQuery(sqlInsertSFCTC.ToString(), false);
StringBuilder sqlInsertSFCTB = new StringBuilder();
sqlInsertSFCTB.Append("insert into SFCTB ");
sqlInsertSFCTB.Append("(COMPANY,CREATOR,USR_GROUP,CREATE_DATE,MODIFIER,MODI_DATE,FLAG,CREATE_TIME,CREATE_AP,CREATE_PRID,MODI_TIME,MODI_AP,MODI_PRID,");
sqlInsertSFCTB.Append("TB001,TB002,TB003,TB004,TB005,TB006,TB007,TB008,TB009,TB010,TB011,TB012,TB013,TB014,TB015,TB016,TB017,TB018,TB019,TB020,");
sqlInsertSFCTB.Append(" TB021,TB022,TB023,TB024,TB025,TB026,TB031,TB036,TB037,TB038,TB039,");
sqlInsertSFCTB.Append("TB200,TB201,TB202)");
sqlInsertSFCTB.Append(" values ( ");
sqlInsertSFCTB.Append("'TECHLINK','BQC01','JG01','" + date + "','BQC01','" + date + "',2,'" + time + "','SFT','Sftb03','" + time + "','SFT','',");
sqlInsertSFCTB.Append("'D201','" + TC002 + "','" + date + "','1','B01','" + TA007 + "','1','B01','" + TA007 + "','TL',0,'N','Y','','" + date + "','ERP','N','','1','',");
sqlInsertSFCTB.Append("'','1','1','','" + month + "',0.2,'0','VND',1,'D201','" + TRANSNO + "',");
sqlInsertSFCTB.Append(int.Parse(output) + int.Parse(NG) + "," + output + "," + NG + " "); //ap them chi dieu yeu cau
sqlInsertSFCTB.Append(")");
sqlInsert.sqlExecuteNonQuery(sqlInsertSFCTB.ToString(), false);
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "InsertdataToERP(string barcode, string output, string NG)", ex.Message);
}
}