private void LoadTreeviewDeptment()
{
TreeNode trnode = new TreeNode("ALL DEPARTMENTS");
trnode.Name = "Node_Depts";
trv_department.Nodes.Clear();
dt = new DataTable();
sqlERPCON conERP = new sqlERPCON();
conERP.sqlDataAdapterFillDatatable("select distinct TC004 from SFCTC ", ref dt);
TreeNode child = new TreeNode();
foreach (DataRow row in dt.Rows)
{
child = new TreeNode(row[0].ToString());
trnode.Nodes.Add(child);
}
trv_department.Nodes.Add(trnode);
trv_department.NodeMouseClick += Trv_department_NodeMouseClick;
trv_department.AfterCheck += Trv_department_AfterCheck;
trnode.Checked = false;
}
private void GetDataFromShipping(DateTime datefrom, DateTime dateto)
{
dt = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append(@"select
coptcs.TC001 as Code_Type,
coptcs.TC002 as Code_No,
coptds.TD004 as Product_Code,
sum(copths.TH008) as Delivery_Quantity,
max(coptgs.TG003) as Delivery_Date
from COPTC coptcs
left join COPTD coptds on coptcs.TC002 = coptds.TD002 and coptcs.TC001 = coptds.TD001 -- cong doan tao don
left join COPTH copths on coptcs.TC002 = copths.TH015 and coptcs.TC001 = copths.TH014 and copths.TH004 =coptds.TD004
left join COPTG coptgs on copths.TH002 = coptgs.TG002 and copths.TH001 = coptgs.TG001 --cong doan giao hang
where 1=1
and coptcs.TC027 = 'Y' and coptgs.TG023 ='Y'
");
sql.Append(" and CONVERT(date,coptds.TD013) >= '" + datefrom + "' ");
sql.Append(" and CONVERT(date,coptds.TD013) <= '" + dateto + "' ");
sql.Append(@"group by
coptcs.CREATE_DATE,
coptcs.TC001 ,
coptcs.TC002 ,
coptds.TD004
");
sql.Append("order by coptcs.TC001, coptcs.TC002");
sqlERPCON con = new sqlERPCON();
con.sqlDataAdapterFillDatatable(sql.ToString(), ref dt);
}
public List <SemiFinishedGoods> ListGetSemiFinishedGoods(string dept, string product)
{
List <SemiFinishedGoods> Listsemifinishedgoods = new List <SemiFinishedGoods>();
try
{
DataTable dtSemi = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append("select MD001,MD003,MD006,MD012 from BOMMD where 1=1 and MD003 like '%B-%' ");//lenh tim kiem chua chat che lam
sql.Append(" and MD001 = '" + product + "'");
sqlERPCON sqlCON = new sqlERPCON();
sqlCON.sqlDataAdapterFillDatatable(sql.ToString(), ref dtSemi);
for (int i = 0; i < dtSemi.Rows.Count; i++)
{
SemiFinishedGoods semiFinished = new SemiFinishedGoods();
// semiFinished.Item = dtSemi.Rows[i]["MD003"].ToString();
semiFinished = GetStockGoodsONSFT(dept, dtSemi.Rows[i]["MD003"].ToString());
Listsemifinishedgoods.Add(semiFinished);
}
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "ListGetSemiFinishedGoods(string product) : " + product, ex.Message);
}
return(Listsemifinishedgoods);
}
public List <SettingBOM> GetSettingBOMs()
{
List <SettingBOM> settingBOMs = new List <SettingBOM>();
try
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@" select distinct ProductName, productNo
from (
select smes.ME002 as Dept,TD001, TD002, TD004 as ProductName,TD005 as productNo,
sum(TD008) as ClientRequestQty,sum(TD009) as DeliveryQty, TD013 as ClientRequestDate from COPTD ptd
inner join COPTC ptc on ptc.TC001 = ptd.TD001 and ptc.TC002 = ptd.TD002
left join CMSME smes on smes.ME001 = ptc.TC005
where ptc.TC027 ='Y' and TD001 like '%B%' and ( TD004 like '%BMH%' or TD004 like '%BWTX%')
group by TD001,TD002,TD005, TD013,smes.ME002,TD004
) DDH
");
sqlERPCON sqlERPCON = new sqlERPCON();
DataTable dt = new DataTable();
sqlERPCON.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
settingBOMs = (from DataRow dr in dt.Rows
select new SettingBOM()
{
ProductName = (dr["ProductName"] != null) ? dr["ProductName"].ToString().Trim() : "",
ProductNo = (dr["productNo"] != null) ? dr["productNo"].ToString().Trim() : "",
}).ToList();
}
catch (Exception)
{
return(null);
}
return(settingBOMs);
}
public List <ItemsInINVMC> GetItemsInINVMCs(string dept, string product)
{
List <ItemsInINVMC> finishedGoods = new List <ItemsInINVMC>();
try
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@" select MC001,MC002,MC007 from INVMC where 1=1 ");
stringBuilder.Append("and MC001 ='" + product + "' ");
DataTable dt = new DataTable();
sqlERPCON sqlERPCON = new sqlERPCON();
sqlERPCON.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (double.Parse(dt.Rows[i]["MC007"].ToString()) > 0)
{
finishedGoods.Add(new ItemsInINVMC
{
Product = dt.Rows[i]["MC001"].ToString(),
Quantity = double.Parse(dt.Rows[i]["MC007"].ToString()),
Warehouse = dt.Rows[i]["MC002"].ToString()
});
}
}
}
catch (Exception ex)
{
return(null);
}
return(finishedGoods);
}
public List <ProductionItem> GetProductionItems(string product, DateTime maxDate)
{
List <ProductionItem> productionItems = new List <ProductionItem>();
try
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@"select TC047 as product, CREATE_DATE, CREATE_TIME ,TC014 as SLNghiemThu, TC016 as SLBaophe
from SFCTC
where 1=1 ");
stringBuilder.Append(" and TC047 like '%" + product + "%'");
stringBuilder.Append(" and CREATE_DATE in ('" + maxDate.ToString("yyyyMMdd") + "', '" + maxDate.AddDays(-1).ToString("yyyyMMdd") + "')");
stringBuilder.Append("order by CREATE_DATE, CREATE_TIME");
DataTable dt = new DataTable();
sqlERPCON sqlERPCON = new sqlERPCON();
sqlERPCON.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
productionItems = (from DataRow dr in dt.Rows
select new ProductionItem()
{
Product = (dr["product"] != null) ? dr["product"].ToString().Trim() : "",
Create_date = (dr["CREATE_DATE"] != null && dr["CREATE_DATE"].ToString() != "") ? DateTime.Parse(dr["CREATE_DATE"].ToString().Trim().Insert(4, "-").Insert(7, "-")) : DateTime.MinValue,
Create_time = (dr["CREATE_TIME"].ToString() != null) ? TimeSpan.Parse(dr["CREATE_TIME"].ToString().Trim()) : TimeSpan.MinValue,
OutputQty = (dr["SLNghiemThu"].ToString() != null) ? double.Parse(dr["SLNghiemThu"].ToString().Trim()) : 0,
DefectQty = (dr["SLBaophe"].ToString() != null) ? double.Parse(dr["SLBaophe"].ToString().Trim()) : 0
}).ToList();
}
catch (Exception)
{
return(null);
}
return(productionItems);
}
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);
}
private void LoadTreeviewDeptment()
{
TreeNode trnode = new TreeNode("ALL DEPARTMENTS");
trnode.Name = "Node_Depts";
trv_department.Nodes.Clear();
dt = new DataTable();
sqlERPCON conERP = new sqlERPCON();
conERP.sqlDataAdapterFillDatatable("select distinct b.TC005,a.ME002 from CMSME a inner join COPTC b on a.ME001 = b.TC005 order by b.TC005 ", ref dt);
TreeNode child = new TreeNode();
foreach (DataRow row in dt.Rows)
{
child = new TreeNode(row[0].ToString() + ": " + row[1].ToString());
trnode.Nodes.Add(child);
}
trv_department.Nodes.Add(trnode);
trv_department.AfterCheck += Trv_department_AfterCheck;
trnode.Checked = true;
}
public List <StockOfSemi> stockOfSemis(string SemiProduct, double rate)
{
List <StockOfSemi> Semis = new List <StockOfSemi>();
try
{
DataTable dt = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append("select MC001,MC002,MC007 from INVMC where (MC002 = 'A03' or MC002 = 'A09') ");
sql.Append(" and MC001 = '" + SemiProduct + "'");
sqlERPCON sqlCON = new sqlERPCON();
sqlCON.sqlDataAdapterFillDatatable(sql.ToString(), ref dt);
Semis = (from DataRow dr in dt.Rows
select new StockOfSemi()
{
Semi = dr["MC001"].ToString().Trim(),
Stock = (dr["MC007"].ToString() != "" && rate != 0) ? Math.Round((double.Parse(dr["MC007"].ToString()) / rate), 0) : 0,
Warehourse = dr["MC002"].ToString().Trim()
}).ToList();
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "stockOfSemis(string SemiProduct, double rate)", ex.Message);
}
return(Semis);
}
// public List<string> ListHENNofProduct(string ProductNO)
// {
// try
// {
// StringBuilder stringBuilder = new StringBuilder();
// stringBuilder.Append(@" select distinct TE017 from MOCTA
// left join MOCTE on TA001 = TE011 and TA002 = TE012
// where (TE018 like '%HENN%' ) and TE019 ='Y'
//");
// stringBuilder.Append(" and TA034 = '"+ ProductNO+"' ");
// sqlERPCON sqlERPCON = new sqlERPCON();
// DataTable dt = new DataTable();
// sqlERPCON.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
// if (dt != null && dt.Rows.Count > 0)
// return dt.AsEnumerable()
// .Select(r => r.Field<string>("TE017"))
// .ToList();
// else return null;
// }
// catch (Exception ex)
// {
// SystemLog.Output(SystemLog.MSG_TYPE.Err, "List<string> ListHENNofProduct(string ProductNO)", ex.Message);
// return null;
// }
// }
public List <string> ListHENNofProduct(string ProductNO)
{
try
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@" select distinct MD003 from BOMMD where 1=1
");
stringBuilder.Append(" and MD001 like '%" + ProductNO + "%' ");
stringBuilder.Append(" and (MD003 like '%BPJHC%' or ( MD003 like '%BPJ-%' and ISNUMERIC(SUBSTRING(MD003,5,1)) = '1' )) ");
stringBuilder.Append(" and MD012 = '" + "" + "'");
sqlERPCON sqlERPCON = new sqlERPCON();
DataTable dt = new DataTable();
sqlERPCON.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
if (dt != null && dt.Rows.Count > 0)
{
return(dt.AsEnumerable()
.Select(r => r.Field <string>("MD003"))
.ToList());
}
else
{
return(null);
}
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "List<string> ListHENNofProduct(string ProductNO)", ex.Message);
return(null);
}
}
private void GetDataFromShipping()
{
//DateTime dtnow = DateTime.Now;
//DateTime datefrom = new DateTime(dtnow.Year, 1, 1);
//DateTime dateto = new DateTime(dtnow.Year, 12, 31);
dt = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append(@"select
TH014, TH015,TH004,
sum(a.TH008) as Delivery_Quantity,
max(b.TG003) as Delivery_Date,
TH016
from COPTH a
inner join COPTG b on a.TH001 = b.TG001 and a.TH002 = b.TG002
where TG023 ='Y' and TH014 !='' and TH015 != ''
");
// sql.Append(" and CONVERT(date,coptds.TD013) >= '" + datefrom + "' ");
// sql.Append(" and CONVERT(date,coptds.TD013) <= '" + dateto + "' ");
sql.Append(@"group by
TH014,
TH015,
TH004,
TH016
");
sql.Append("order by TH014, TH015, TH004");
sqlERPCON con = new sqlERPCON();
con.sqlDataAdapterFillDatatable(sql.ToString(), ref dt);
}
public List <BOMItems> bOMItems(string NameProduct)
{
List <BOMItems> bOMItems = new List <BOMItems>();
try
{
DataTable dt = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append("select MD001,MD003,MD006,MD012 from BOMMD where 1=1 and MD003 like '%B-%' ");
sql.Append(" and MD001 = '" + NameProduct + "'");
sqlERPCON sqlCON = new sqlERPCON();
sqlCON.sqlDataAdapterFillDatatable(sql.ToString(), ref dt);
bOMItems = (from DataRow dr in dt.Rows
select new BOMItems()
{
finishedGoods = dr["MD001"].ToString().Trim(),
SemiFinishedgoods = dr["MD003"].ToString().Trim(),
Rate = (dr["MD006"].ToString() != "") ? double.Parse(dr["MD006"].ToString().Trim()) : 0,
Expired = (dr["MD012"].ToString() != "") ? DateTime.Parse(dr["MD012"].ToString().Trim().Insert(4, "-").Insert(7, "-")) : DateTime.MaxValue
}).ToList();
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "bOMItems (string NameProduct)", ex.Message);
}
return(bOMItems);
}
public bool IscheckFIFO(string material, string lot, string warehouse)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@"
select ME009,a.* from INVMM a
inner join INVME b on ME001 = MM001 and ME002= MM004
where 1=1
");
stringBuilder.Append(" and MM002 = '" + warehouse + "' ");
stringBuilder.Append(" and MM001 = '" + material + "' ");
stringBuilder.Append(@" AND MM005 > 0
order by CAST(ME009 as datetime) DESC ");
DataTable dt = new DataTable();
sqlERPCON sqlERPCON = new sqlERPCON();
sqlERPCON.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
var dtrow = dt.AsEnumerable().Where(d => d.Field <string>("MM004").Trim() == lot).ToList();
if (dtrow.Count == 1)
{
DateTime dtExpiryRow = DateTime.ParseExact(dtrow[0]["ME009"].ToString(), "yyyyMMdd", CultureInfo.CurrentCulture);
for (int i = 0; i < dt.Rows.Count; i++)
{
DateTime dtExpiryi = DateTime.ParseExact(dt.Rows[i]["ME009"].ToString(), "yyyyMMdd", CultureInfo.CurrentCulture);
if (dtExpiryi < dtExpiryRow)
{
return(false);
}
}
}
return(true);
}
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 <string> ListHENNofProduct(string ProductNO)
{
try
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@" select distinct TE017 from MOCTA
left join MOCTE on TA001 = TE011 and TA002 = TE012
where (TE018 like '%HENN%' ) and TE019 ='Y'
");
stringBuilder.Append(" and TA034 = '" + ProductNO + "' ");
sqlERPCON sqlERPCON = new sqlERPCON();
DataTable dt = new DataTable();
sqlERPCON.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
if (dt != null && dt.Rows.Count > 0)
{
return(dt.AsEnumerable()
.Select(r => r.Field <string>("TE017"))
.ToList());
}
else
{
return(null);
}
}
catch (Exception ex)
{
return(null);
}
}
public DataTable GetDataTableBOMHQonERP(string ma_SP)
{
DataTable dt = new DataTable();
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@" with BOMLevel1 as
(select d.MD001 ,d.MD002, d.MD003 ,d.MD004,cast(MD006/MD007 as decimal(10,3)) as Amoutused, 1 as [Level]
from BOMMC c
inner join BOMMD d on MC001 = MD001 ");
stringBuilder.Append(" where MC001 = '" + ma_SP + "' ");
stringBuilder.Append(@" union all
select d.MD001,d.MD002, d.MD003 ,d.MD004,cast(MD006/MD007 as decimal(10,3)) as Amoutused, dlevel.Level+1
from BOMMD d
inner join BOMLevel1 dlevel on d.MD001 = dlevel.MD003
)
select distinct d.MB201 as MA_NVL,f.XA003 as HS_CODE, cast( SUM(c.Amoutused/e.MC004 ) as decimal(10,3)) as DM_SD , c.MD004 as MA_DVT from BOMLevel1 c
inner join INVMB d on MB001 = c.MD003
inner join BOMMC e on c.MD001 = e.MC001
inner join BOMXA f on d.MB201 = f.XA001
WHERE Level < 2 AND MB201 != ''
group by d.MB201, c.MD004,c.Level, f.XA003 ");
sqlERPCON sqlERPCON = new sqlERPCON();
sqlERPCON.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
return(dt);
}
public List <Accessory> GetAccessories(string dept, string product)
{
List <Accessory> accessories = new List <Accessory>();
try
{
//DataTable dt = new DataTable();
//StringBuilder stringBuilder = new StringBuilder();
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@" select MD003,MC002,MC007 from BOMMD
inner join INVMC on MD003 = MC001
where 1=1 and MD012 ='' and MC007 > '0'
");
stringBuilder.Append(" and MD001 like '%" + product + "%' ");
if (dept == "B01-MH" || dept == "B01-FF")
{
if (product.Contains("BMH") || product.Contains("BWT") || product.Contains("FF"))
{
stringBuilder.Append(" and (MD003 like '%BPJHC%' or ( MD003 like '%BPJ-%' and ISNUMERIC(SUBSTRING(MD003,5,1)) = '1' )) ");
stringBuilder.Append(" and ( MC002 = 'Y22' or MC002 = 'Y12' ) ");
}
}
else if (dept == "A01-Gia Dung")
{
stringBuilder.Append(" and (MD003 like 'ABL%' or MD003 like 'AWJ%' ) ");
stringBuilder.Append(" and (MC002 = 'A07' or MC002 ='A01' or MC002 = 'W01' or MC002 = '002') ");
}
else if (dept == "A01-PTC")
{
stringBuilder.Append(" and (MD003 like 'ABL%' or MD003 like 'AWJ%' ) ");
stringBuilder.Append(" and (MC002 = 'A07' or MC002 ='A01' or MC002 = 'W01' or MC002 = '002') ");
}
else if (dept == "A01-JM")
{
stringBuilder.Append(" and (MD003 like 'ABL%' or MD003 like 'AWJ%' ) ");
stringBuilder.Append(" and (MC002 = 'A07' or MC002 ='A01' or MC002 = 'W01' or MC002 = '002') ");
}
sqlERPCON sqlERPCON = new sqlERPCON();
DataTable dt = new DataTable();
sqlERPCON.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
Accessory accessory = new Accessory();
accessory.Item = dt.Rows[i]["MD003"].ToString();
accessory.Warehouse = dt.Rows[i]["MC002"].ToString();
accessory.QtyInWarehouse = double.Parse(dt.Rows[i]["MC007"].ToString().Trim());
accessories.Add(accessory);
}
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, " List<Accessory> GetAccessories: " + product, ex.Message);
}
return(accessories);
}
public List <ItemMaterial> ListMaterial(string MaBP)
{
List <ItemMaterial> itemMaterials = new List <ItemMaterial>();
DateTime date = DateTime.Now.AddDays(0).Date;
try
{
StringBuilder stringBuilder = new StringBuilder();
// stringBuilder.Append()
stringBuilder.Append(@"select distinct TC004,TC005,TC007,TC047,TB003 from SFCTC a
inner join SFCTB on TC001 = TB001 and TC002 = TB002
where 1=1 ");
stringBuilder.Append("and CONVERT(date, TB003) >= '" + date.ToString("yyyyMMdd") + "'");
sqlERPCON sql = new sqlERPCON();
DataTable dt = new DataTable();
sql.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
var InforProduction = (from DataRow dr in dt.Rows
select new ItemMaterial()
{
deptCode = dr["TC007"].ToString(),
IDCode = dr["TC004"].ToString(),
IDName = dr["TC005"].ToString(),
ID = dr["TC004"].ToString() + "-" + dr["TC005"].ToString(),
Product = dr["TC047"].ToString(),
DateRun = (dr["TB003"].ToString() != "") ? DateTime.Parse(dr["TB003"].ToString().Insert(4, "-").Insert(7, "-")) : DateTime.MinValue
}).ToList();
// itemMaterials = InforProduction;
bool ISNVL = false; bool isSL = false;
List <MaterialItems> materialAdapts = new List <MaterialItems>();
foreach (var item in InforProduction)
{
Material material = new Material();
double Qty = 0;
var IsCheckNVL = material.IsMaterialOK(item.IDCode, item.IDName, out ISNVL, out materialAdapts, out Qty);
item.Quantity = Qty;
if (ISNVL == false)
{
item.MaterialAdapts = materialAdapts;
itemMaterials.Add(item);
}
}
}
catch (Exception ex)
{
throw;
}
return(itemMaterials);
}
private void GetDataProduction()
{
DateTime dateto = dtp_to.Value;
DateTime datefrom = dtp_from.Value;
dtProduction = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append(@"select
b.TB001 as Ma,
b.TB002 as MaNo,
c.TC004 as LSX,
c.TC005 as LSXNo,
c.TC047 as MaSP,
c.TC048 as TenSP,
b.TB005 as bophanchuyen,
b.TB006 as tenbophanchuyen,
b.TB010 as maxuong,
CONVERT(date,b.TB015) as Ngay,
CONVERT(time,b.CREATE_TIME) as gio,
c.TC014 as SLNhiemthu,
c.TC016 as SLBaoPhe,
c.TC036 as SL,
c.TC041 as BoPhanChuyenNhap,
c.TC038 as ngayNghiemthu,
c.TC042 as SLDongGoiChuyenCD,
c.TC043 as SLDGNT,
c.TC044 as SLDGTRaLai
from SFCTB b
left join SFCTC c on b.TB001 = c.TC001 and b.TB002 = c.TC002
where 1=1 and c.TC004 != '' and c.TC005 != '' ");
if (ChooseDeptoSQLcommand(trv_department) != "")
{
sql.Append(ChooseDeptoSQLcommand(trv_department));
}
else
{
MessageBox.Show("Please choose departments which you want to search data ! ", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
sql.Append(" and c.TC041 = '" + cb_Product.SelectedItem.ToString() + "' ");
sql.Append(" and CONVERT(date,b.TB015) >= '" + datefrom + "' ");
sql.Append(" and CONVERT(date,b.TB015) <= '" + dateto + "' ");
sql.Append("order by b.TB015");
sqlERPCON con = new sqlERPCON();
con.sqlDataAdapterFillDatatable(sql.ToString(), ref dtProduction);
}
public DataTable GetdtMOCTCTE(string type, string No)
{
DataTable dt = new DataTable();
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@"select TE001, TE002, TE003, TE004, TE005,TE006,TE007, TE008, TE009,TE010, TE011, TE012, TE017, TE029, TC009 from MOCTC a
inner join MOCTE b on TC001 = TE001 and TC002 = TE002
where 1 = 1 ");
stringBuilder.Append(" and TC001 ='" + type + "' ");
stringBuilder.Append(" and TC002 ='" + No + "' ");
sqlERPCON sqlERPCON = new sqlERPCON();
sqlERPCON.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
return(dt);
}
public DataTable GetDataProductionOrder2(DateTime todate)
{
try
{
// DateTime dtnow = DateTime.Now;
//DateTime datefrom = new DateTime(dtnow.Year, 1, 1);
DateTime dateto = todate;
dtShipping = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append(@"select
coptcs.TC001 as Code_Type,
coptcs.TC002 as Code_No,
cmsmes.ME002 as Department_code,
copmas.MA002 as Clients_Name,
coptcs.TC012 as Clients_Order_Code,
coptds.TD004 as Product_Code,
coptds.TD005 as Product_Name,
coptds.TD010 as Unit,
coptcs.TC005 as Department,
coptds.TD013 as Client_Request_Date,
coptds.TD008 as Order_Quantity,
coptds.TD009 as Shipped_Quantity,
coptds.TD016 as StatusOFCode,
invmbs.MB064 as Stock_Qty,
coptds.TD003 as STT
from COPTC coptcs
left join COPTD coptds on coptcs.TC002 = coptds.TD002 and coptcs.TC001 = coptds.TD001 -- cong doan tao don
inner join COPMA copmas on copmas.MA001 = coptcs.TC004
left join INVMB invmbs on invmbs.MB001 = coptds.TD004
left join CMSME cmsmes on cmsmes.ME001 = coptcs.TC005
where 1=1
and coptcs.TC027 = 'Y' and coptds.TD016 ='N' and coptds.TD009 < coptds.TD008
");
// sql.Append(" and CONVERT(date,coptds.TD013) >= '" + datefrom + "' ");
sql.Append(" and CONVERT(date,coptds.TD013) <= '" + dateto.ToString("yyyyMMdd") + "' ");
sql.Append("order by coptds.TD013");
sqlERPCON con = new sqlERPCON();
con.sqlDataAdapterFillDatatable(sql.ToString(), ref dtShipping);
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "GetDataProductionOrder() : ", ex.Message);
}
return(dtShipping);
}
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'
or TA004 = 'B02'
and TA011+TA012 <TA010
and CAST(TA008 AS date ) >= '20200101'
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);
}
}
private void GetDataProductionOrder(DateTime datefrom, DateTime dateto)
{
try
{
dtShipping = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append(@"select
coptcs.TC001 as Code_Type,
coptcs.TC002 as Code_No,
cmsmes.ME002 as Department_code,
copmas.MA002 as Clients_Name,
coptcs.TC012 as Clients_Order_Code,
coptds.TD004 as Product_Code,
coptds.TD005 as Product_Name,
coptds.TD010 as Unit,
coptcs.TC005 as Department,
coptds.TD013 as Client_Request_Date,
coptds.TD008 as Order_Quantity,
coptds.TD009 as Shipped_Quantity,
coptds.TD016 as StatusOFCode,
invmbs.MB064 as Stock_Qty
from COPTC coptcs
left join COPTD coptds on coptcs.TC002 = coptds.TD002 and coptcs.TC001 = coptds.TD001 -- cong doan tao don
inner join COPMA copmas on copmas.MA001 = coptcs.TC004
left join INVMB invmbs on invmbs.MB001 = coptds.TD004
left join CMSME cmsmes on cmsmes.ME001 = coptcs.TC005
where 1=1
and coptcs.TC027 = 'Y'
");
sql.Append(" and CONVERT(date,coptds.TD013) >= '" + datefrom + "' ");
sql.Append(" and CONVERT(date,coptds.TD013) <= '" + dateto + "' ");
sql.Append("order by coptds.TD013");
sqlERPCON con = new sqlERPCON();
con.sqlDataAdapterFillDatatable(sql.ToString(), ref dtShipping);
}
catch (Exception ex)
{
Log.Logfile.Output(Log.StatusLog.Error, "GetDataProductionOrder() : " + ex.Message);
}
}
public List <string> ListProduct(string dept)
{
List <string> listproduct = new List <string>();
List <DeptCodeName> deptCodes = new List <DeptCodeName>();
StringBuilder sql = new StringBuilder();
sql.Append(@"SELECT DISTINCT TC047 FROM SFCTA
left join SFCTC on TA001 = TC004 and TA002 = TC005 ");
sql.Append("where 1=1 and TA011+TA012 <TA010 ");
sql.Append("and TA004 = '" + dept + "'");
sqlERPCON sqlERPCON = new sqlERPCON();
DataTable dt = new DataTable();
sqlERPCON.sqlDataAdapterFillDatatable(sql.ToString(), ref dt);
listproduct = dt.AsEnumerable()
.Select(r => r.Field <string>("TC047"))
.ToList();
return(listproduct);
}
public List <OrderVariable> LoadOrderInformationbyDate(DateTime from, DateTime to, string Dept)
{
List <OrderVariable> orderVariables = new List <OrderVariable>();
try
{
DataTable dt = new DataTable();
sqlERPCON sqlERPCON = new sqlERPCON();
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@"select smes.ME002 as Dept,TD001 + '-' + TD002 as DDH, TD004 as product ,TD005 as productName,
sum(TD008) as QuantityOfOrder, sum(TD009) as QuantityOfDelivery, TD013 as ClientRequestDate from COPTD ptd
inner join COPTC ptc on ptc.TC001 = ptd.TD001 and ptc.TC002 = ptd.TD002
left join CMSME smes on smes.ME001 = ptc.TC005
where ptc.TC027 ='Y' and TD001 like '%B%' and ( TD004 like '%BMH%' or TD004 like '%BWTX%')
");
stringBuilder.Append(" and CONVERT(date,ptd.TD013) >= '" + from.ToString("yyyyMMdd") + "' ");
stringBuilder.Append(" and CONVERT(date,ptd.TD013) <= '" + to.ToString("yyyyMMdd") + "' ");
stringBuilder.Append(@" group by TD001,TD002,TD005, TD013,smes.ME002,TD004
order by TD013 ");
sqlERPCON.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
orderVariables = (from DataRow dr in dt.Rows
select new OrderVariable()
{
Dept = (dr["Dept"] != null) ? dr["Dept"].ToString().Trim() : "",
DDH = (dr["DDH"] != null) ? dr["DDH"].ToString().Trim() : "",
Product = (dr["product"] != null) ? dr["product"].ToString().Trim() : "",
ProductName = (dr["productName"] != null) ? dr["productName"].ToString().Trim() : "",
ClientOrderQty = (dr["QuantityOfOrder"] != null && dr["QuantityOfOrder"].ToString() != "") ? (double.Parse(dr["QuantityOfOrder"].ToString().Trim())) : 0,
DeliveryQty = (dr["QuantityOfDelivery"] != null && dr["QuantityOfDelivery"].ToString() != "") ? (double.Parse(dr["QuantityOfDelivery"].ToString())) : 0,
ClientRequestDate = (dr["ClientRequestDate"] != null && dr["ClientRequestDate"].ToString() != "") ? DateTime.Parse(dr["ClientRequestDate"].ToString().Trim().Insert(4, "-").Insert(7, "-")) : DateTime.MinValue
}).ToList();
}
catch (Exception)
{
return(null);
}
return(orderVariables);
}
private void btn_search_Click(object sender, EventArgs e)
{
StringBuilder sql = new StringBuilder();
sql.Append(@"select a.Row, a.TENKHO, a.MANVL, a.TENSP, a.QUYCACH, a.DVTONKHO, a.SLANTOAN, a.SLTONKHO,a.CHENHLECH, substring(cast(a.PHANTRAM as varchar(100)),0,5) as PHAMTRAM,
a.Status, b.DONXINMUA, CAST(cast(b.NGAYXINMUA as date) as varchar(10)) as NGAYXINMUA, b.SLXINMU, b.DONMUA,cast( b.NGAYMUA as date) as NGAYMUA,b.SLMUA,b.SLCHUAGIAO, cast( b.NgayGiaoHangDK as date) as NgayGiaoHangDK ,
QuocGia
from (
select ROW_NUMBER() OVER(order by ROUND(((C.MC007 - 0) / C.MC004)*100, 3) asc) AS Row,
C.MC002 AS TENKHO, C.MC001 AS MANVL, B.MB002 AS TENSP,B.MB003 AS QUYCACH, B.MB004 AS DVTONKHO,
C.MC004 AS SLANTOAN, C.MC007 AS SLTONKHO, (C.MC007 - C.MC004) AS CHENHLECH, ROUND(((C.MC007 - 0) / C.MC004)*100, 3) as PHANTRAM,
CASE when ROUND(((C.MC007 - 0) / C.MC004)*100, 3) > 200 then 'High'
WHEN ROUND(((C.MC007-0)/ C.MC004),3)*100 >= 100 and ROUND(((C.MC007-0)/ C.MC004)*100,3) <= 200 then 'OK'
WHEN ROUND(((C.MC007-0)/ C.MC004),3)*100 < 100 then 'Not Enough' end as 'Status'
from INVMC C
LEFT JOIN INVMB B ON C.MC001 = B.MB001
WHERE C.MC004 != 0 AND(C.MC002 LIKE 'D%' OR C.MC002 LIKE 'B%')
) as a
left join
(
(select purtb.TB004 as MANVL, purta.TA001 + '-' + purta.TA002 as DONXINMUA,
purta.TA013 as NGAYXINMUA,
purtb.TB009 as SLXINMU,
DONMUA = null,
NGAYMUA = null,
SLMUA = null,SLCHUAGIAO = null,
NgayGiaoHangDK = null,
QuocGia = null
From PURTA purta
left join PURTB purtb on purta.TA001 = purtb.TB001 and purta.TA002 = purtb.TB002
where 1 = 1
--and TB004 = 'BPJHC55-AIR-FKM'
and purta.TA007 = 'Y' and purtb.TB022 like '' and
purtb.TB039 = 'N')
union all
(select purtb.TB004 as MANVL, purta.TA001 + '-' + purta.TA002 as DONXINMUA,
purta.TA013 as NGAYXINMUA,
purtb.TB009 as SLXINMU,
purtc.TC001 + '-' + purtc.TC002 as DONMUA,
NGAYMUA = purtc.TC024,
purtd.TD008 as SLMUA, (purtd.TD008 - purtd.TD015) as SLCHUAGIAO,
purtd.TD012 as NgayGiaoHangDK,
cmsmr.MR004 as QuocGia
From PURTA purta
left join PURTB purtb on purta.TA001 = purtb.TB001 and purta.TA002 = purtb.TB002
left join PURTD purtd on purtb.TB022 = (purtd.TD001 + '-' + purtd.TD002 + '-' + purtd.TD003)
left join PURTC purtc on purtc.TC001 = purtd.TD001 and purtc.TC002 = purtd.TD002
left join PURMA purma on purtc.TC004 = purma.MA001
left join CMSMR cmsmr on cmsmr.MR002 = purma.MA006
where 1 = 1
--and TB004 = 'BPJHC55-AIR-FKM'
and purta.TA007 = 'Y'
and purtd.TD016 = 'N'
and purtc.TC014 = 'Y')
) b on a.MANVL = b.MANVL where 1=1
");
//
if (cmb_warehouseno.Text != "")
{
sql.Append(" and TENKHO = '" + cmb_warehouseno.Text + "'");
}
if (cmb_status.Text != "")
{
sql.Append("and Status = '" + cmb_status.Text + "'");
}
if (txt_materialcode.Text != "")
{
sql.Append(" and a.MANVL like '%" + txt_materialcode.Text + "%'");
}
if (txt_materialname.Text != "")
{
sql.Append(" and a.TENSP like '%" + txt_materialname.Text + "%'");
}
if (cmb_nation.Text != "")
{
sql.Append(" and QuocGia = '" + cmb_nation.Text + "'");
}
sql.Append(" order by a.Row");
DataTable dt = new DataTable();
sqlERPCON sqlcon = new sqlERPCON();
sqlcon.sqlDataAdapterFillDatatable(sql.ToString(), ref dt);
dgv_show.DataSource = dt;
changeHeaderName(ref dgv_show);
}
private void btn_search_Click(object sender, EventArgs e)
{
StringBuilder sql = new StringBuilder();
DataTable dt = new DataTable();
//sql.Append("select ROW_NUMBER() OVER(order by CAST(ME009 as date)asc) AS Row,ME001, ME002, ME003, ME005, ME006, ME010,");
//sql.Append("CAST(ME009 as datetime) as newdate,");
//sql.Append(" DATEDIFF(dayofyear, GETDATE(),CAST(ME009 as datetime)),");
//sql.Append(" case when CAST(ME009 as date) > GETDATE()+10 then 'OK' ");
//sql.Append(" when CAST(ME009 as date) >= GETDATE() and CAST(ME009 as date) <= GETDATE()+10 then 'ALARM' ");
//sql.Append(" else 'NG' end as STATUS");
//sql.Append(" from INVME where ME009 not like '' and ME009 like '201%'");
sql.Append(@"SELECT ROW_NUMBER() OVER(order by G1.DIFFIRENT) as ROWS, G1.ME001, G2.MB023, G1.ME002 ,G1.DATET ,
G1.ME005, G1.ME006, G1.STATUS, cast(G1.HSD as varchar(10)) as HSD,
G1.DIFFIRENT, G1.LF005, G1.TONKHO
FROM
(
SELECT ROW_NUMBER() OVER(order by b1.DIFFIRENT) as ROWS, b1.ME001, b1.ME002,CAST(CAST( b1.ME003 AS DATE) as varchar(10)) AS DATET ,
b1.ME005, b1.ME006, b1.STATUS, b1.newdate AS HSD ,
b1.DIFFIRENT, b2.LF005, (b2.SLNHAP - b2.SLXUAT ) AS TONKHO
FROM
(select ME001, ME002, ME003, ME005, ME006, ME010,CAST(ME009 as date) as newdate,
DATEDIFF(dayofyear, GETDATE(),CAST(ME009 as datetime)) AS DIFFIRENT,
case
when CAST(ME009 as date) > GETDATE()+10 then 'OK'
when CAST(ME009 as date) >= GETDATE() and CAST(ME009 as date) <= GETDATE()+10 then 'ALARM'
else 'NG' end as STATUS
from INVME
where ME009 not like '' and ME009 like '201%' ) AS b1
inner JOIN
(
SELECT A.LF004, A.LF005, A.LF007, SLNHAP, SLXUAT FROM
(select LF004, LF005, LF007, LF008, SUM (LF011) AS SLNHAP from INVLF
WHERE 1=1
AND LF008 =1
GROUP BY LF004, LF005, LF007, LF008 ) AS A
LEFT JOIN
(select LF004, LF005, LF007, LF008,SUM (LF011) AS SLXUAT from INVLF
WHERE 1=1
AND LF008 =-1
GROUP BY LF004, LF005, LF007, LF008) AS B ON A.LF007= B.LF007 AND A.LF004 = B.LF004 and A.LF005 = B.LF005
WHERE 1=1
AND SLNHAP > SLXUAT
) AS b2 ON b1.ME001 = b2.LF004 and b1.ME002 = b2.LF007
) AS G1
LEFT JOIN INVMB G2 ON G1.ME001 = G2.MB001
where 1=1
");
if (txt_materialcode.Text != "")
{
sql.Append(" and G1.ME001 like '%" + txt_materialcode.Text + "%'");
}
else if (txt_Lot.Text != "")
{
sql.Append(" and G1.ME002 like '%" + txt_Lot.Text + "%'");
}
else if (cmb_warehousecode.Text != "")
{
sql.Append(" and G1.LF005 like '%" + cmb_warehousecode.Text + "%'");
}
else
{
sql.Append(" and G1.HSD <= '" + dtp_to.Value + "'");
}
sqlERPCON sqlcon = new sqlERPCON();
sqlcon.sqlDataAdapterFillDatatable(sql.ToString(), ref dt);
dgv_show.DataSource = dt;
changeHeaderName(ref dgv_show);
}
void getERPdata()
{
DateTime dateto = dtp_to.Value.Date;
DateTime datefrom = dtp_from.Value.Date;
dt = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append(@"
select
CONVERT(date,moctas.CREATE_DATE) as Create_Date,
moctas.TA026 as Code_Type,
moctas.TA027 as Code_No,
moctas.TA001 as Production_Planning_Code,
moctas.TA002 as Production_Planning_No,
moctas.TA006 as Product_Code,
moctas.TA034 as Product_Name,
moctas.TA009 as Production_Start_Date,
moctas.TA010 as Estimate_Complete_Date,
moctas.TA012 as Actual_Production_Date,
moctas.TA013 as Confirm,
moctas.TA015 as Plan_Quanity,
sum(moctgs.TG011) as Finished_Goods,
sum(moctgs.TG012) as NG_Quanity,
sum(moctgs.TG013) as Good_Quanity,
moctgs.TG007 as Unit,
max(CONVERT(date,moctfs.TF003)) as Input_Date
from MOCTA moctas
left join MOCTG moctgs on moctgs.TG014 = moctas.TA001 and moctgs.TG015 = moctas.TA002
left join MOCTF moctfs on moctfs.TF001 = moctgs.TG001 and moctfs.TF002 = moctgs.TG002
where 1=1 ");
if ((string)cmb_COPTC_TC001.Text != "")
{
sql.Append(" and moctas.TA026 = '" + (string)cmb_COPTC_TC001.Text + "'");
}
if (cmb_MOCTA_TA001.Text != "")
{
sql.Append(" and moctas.TA001 = '" + cmb_MOCTA_TA001.Text + "'");
}
if (cmb_MOCTA_TA002.Text != "")
{
sql.Append(" and moctas.TA002 = '" + cmb_MOCTA_TA002.Text + "'");
}
if ((string)cmb_COPTC_TC002.Text != "")
{
sql.Append(" and moctas.TA027 = '" + (string)cmb_COPTC_TC002.Text + "'");
}
else
{
sql.Append(" and CONVERT(date,moctas.CREATE_DATE) >= '" + datefrom + "'");
sql.Append(" and CONVERT(date,moctas.CREATE_DATE) <= '" + dateto + "'");
}
sql.Append(@" group by
moctas.CREATE_DATE,
moctas.TA001 ,
moctas.TA002 ,
moctas.TA006,
moctas.TA009 ,
moctas.TA010 ,
moctas.TA012 ,
moctas.TA013 ,
moctas.TA015,
moctas.TA034,
moctas.TA026 ,
moctas.TA027,
moctgs.TG007 ");
sql.Append(" order by moctas.TA002");
sqlERPCON con = new sqlERPCON();
con.sqlDataAdapterFillDatatable(sql.ToString(), ref dt);
//checkdata
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string MaTaoDon = dt.Rows[i]["Code_Type"].ToString().Replace("'", "");
string codeTaoLenh = dt.Rows[i]["Code_No"].ToString().Replace("'", "");
string sqlcheck = "select COUNT(*) from t_OCTB where TB02 = '" + MaTaoDon + "' and TB03 ='" + codeTaoLenh + "'";
double FinishedGoodQty = 0;
double NGQty = 0;
double OKQty = 0;
double PercentofOKQty = 0;
try
{
FinishedGoodQty = double.Parse(dt.Rows[i]["Finished_Goods"].ToString().Replace("'", ""));
NGQty = double.Parse(dt.Rows[i]["NG_Quanity"].ToString().Replace("'", ""));
OKQty = double.Parse(dt.Rows[i]["Good_Quanity"].ToString().Replace("'", ""));
if (FinishedGoodQty != 0)
{
PercentofOKQty = Math.Round((OKQty / FinishedGoodQty) * 100, 2);
}
}
catch (Exception ex)
{
FinishedGoodQty = 0;
NGQty = 0;
OKQty = 0;
SystemLog.Output(SystemLog.MSG_TYPE.Err, "getERPdata()", ex.Message);
//MessageBox.Show(ex.Message);
}
sqlCON check = new sqlCON();
if (int.Parse(check.sqlExecuteScalarString(sqlcheck)) == 0) //insert
{
string list = "";
for (int j = 0; j < dt.Columns.Count; j++)
{
list += "'";
list += dt.Rows[i][j].ToString() + "',";
}
StringBuilder sqlinsert = new StringBuilder();
sqlinsert.Append("insert into t_OCTB ");
sqlinsert.Append(@"(TB01,TB02,TB03,TB04,TB05,TB06,TB07,TB08,TB09,TB10,TB11,TB12,TB13,TB14,TB15,TB16,TB17,TB31,TB32,TB33,UserName,datetimeRST) values ( ");
sqlinsert.Append(list);
if (PercentofOKQty >= 100)
{
sqlinsert.Append("'OK', '" + PercentofOKQty.ToString() + " ' , '0' " + ",");
}
else if (PercentofOKQty > 98)
{
sqlinsert.Append("'OK', '" + PercentofOKQty.ToString() + " ' , '1' " + ",");
}
else if (PercentofOKQty < 98 && PercentofOKQty > 95)
{
sqlinsert.Append("'OK', '" + PercentofOKQty.ToString() + " ' , '2' " + ",");
}
else if (PercentofOKQty < 95)
{
sqlinsert.Append("'NG', '" + PercentofOKQty.ToString() + " ' , '0' " + ",");
}
sqlinsert.Append("'" + Class.valiballecommon.GetStorage().UserName + "',GETDATE())");
sqlCON insert = new sqlCON();
insert.sqlExecuteNonQuery(sqlinsert.ToString(), false);
}
else //update
{
StringBuilder sqlupdate = new StringBuilder();
sqlupdate.Append("update t_OCTB set ");
sqlupdate.Append(@"TB13 = '" + dt.Rows[i]["Finished_Goods"].ToString() + "',");
sqlupdate.Append(@"TB14 = '" + dt.Rows[i]["NG_Quanity"].ToString() + "',");
sqlupdate.Append(@"TB15 = '" + dt.Rows[i]["Good_Quanity"].ToString() + "',");
sqlupdate.Append(@"TB17 = '" + dt.Rows[i]["Input_Date"].ToString() + "'");
if (PercentofOKQty >= 100)
{
sqlupdate.Append(@", TB31 = 'OK' ,");
sqlupdate.Append(@" TB32 = ' " + PercentofOKQty.ToString() + "' ,");
sqlupdate.Append(@"TB33 = '0' ");
}
else if (PercentofOKQty > 98)
{
sqlupdate.Append(@", TB31 = 'OK' ,");
sqlupdate.Append(@" TB32 = ' " + PercentofOKQty.ToString() + "' ,");
sqlupdate.Append(@"TB33 = '1' ");
}
else if (PercentofOKQty < 98 && PercentofOKQty > 95)
{
sqlupdate.Append(@", TB31 = 'OK' ,");
sqlupdate.Append(@" TB32 = ' " + PercentofOKQty.ToString() + "' ,");
sqlupdate.Append(@"TB33 = '2' ");
}
else if (PercentofOKQty < 95)
{
sqlupdate.Append(@", TB31 = 'NG' ,");
sqlupdate.Append(@" TB32 = ' " + PercentofOKQty.ToString() + "' ,");
sqlupdate.Append(@"TB33 = '0' ");
}
// sqlupdate.Append("'" + Class.valiballecommon.GetStorage().UserName + "',GETDATE())");
sqlupdate.Append(@" where TB02 = '" + MaTaoDon + "' and TB03 ='" + codeTaoLenh + "'");
sqlCON update = new sqlCON();
update.sqlExecuteNonQuery(sqlupdate.ToString(), false);
}
}
}
}
void getERPdata()
{
DateTime dateto = dtp_to.Value.Date;
DateTime datefrom = dtp_from.Value.Date;
dt = new DataTable();
StringBuilder sql = new StringBuilder();
sql.Append(@"
select distinct
CONVERT(date,moctas.CREATE_DATE) as Create_Date,
moctas.TA026 as Code_Type,
moctas.TA027 as Code_No,
moctas.TA001 as Production_Planning_Code,
moctas.TA002 as Production_Planning_No,
moctas.TA006 as Product_Code,
moctas.TA034 as Product_Name,
moctas.TA009 as Production_Start_Date,
moctas.TA010 as Estimate_Complete_Date,
moctas.TA012 as Actual_Production_Date,
moctas.TA013 as Confirm,
moctas.TA015 as Product_Quanity,
moctbs.TB003 as Material_Code,
moctbs.TB012 as Material_Name,
moctbs.TB009 as Warehourse_Code,
moctbs.TB015 as Ready_Material_Date,
moctbs.TB004 as amount_of_material_receive,
moctbs.TB005 as amount_of_material_use,
invmbs.MB064 as Avaiable_Material_Quanity,
sum(moctes.TE005) as Production_Material_Quantity,
moctbs.TB007 as Unit
from MOCTA moctas
left join MOCTB moctbs on moctas.TA001 = moctbs.TB001 and moctas.TA002 = moctbs.TB002
left join INVMB invmbs on moctbs.TB003 = invmbs.MB001
left join MOCTE moctes on moctes.TE004 =moctbs.TB003 and moctes.TE011 =moctas.TA001 and moctes.TE012 =moctas.TA002
where 1=1 and moctes.TE001 not like '%6%' ");
if ((string)cmb_MOCTA_TA001.Text != "")
{
sql.Append(" and moctas.TA001 = '" + (string)cmb_MOCTA_TA001.Text + "'");
}
if ((string)cmb_MOCTA_TA002.Text != "")
{
sql.Append(" and moctas.TA002 = '" + (string)cmb_MOCTA_TA002.Text + "'");
}
if ((string)cmb_COPTC_TC001.Text != "")
{
sql.Append(" and moctas.TA026 = '" + (string)cmb_COPTC_TC001.Text + "'");
}
if ((string)cmb_COPTC_TC002.Text != "")
{
sql.Append(" and moctas.TA027 = '" + (string)cmb_COPTC_TC002.Text + "'");
}
else
{
sql.Append(" and CONVERT(date,moctas.CREATE_DATE) >= '" + datefrom + "' ");
sql.Append(" and CONVERT(date,moctas.CREATE_DATE) <= '" + dateto + "' ");
}
sql.Append(@" group by
moctas.CREATE_DATE,
moctas.TA001 ,
moctas.TA002 ,
moctas.TA003,
moctas.TA006,
moctas.TA009 ,
moctas.TA010 ,
moctas.TA012 ,
moctas.TA013 ,
moctas.TA015,
moctas.TA024 ,
moctas.TA025,
moctas.TA026 ,
moctas.TA027,
moctas.TA034,
moctbs.TB003,
moctbs.TB012,
moctbs.TB009,
moctbs.TB015,
moctbs.TB004,
moctbs.TB005,
moctbs.TB007,
invmbs.MB064
");
sql.Append(" order by moctas.TA002");
sqlERPCON con = new sqlERPCON();
con.sqlDataAdapterFillDatatable(sql.ToString(), ref dt);
// checkdata
if (dt.Rows.Count > 0)
{
try
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string sqlcheck = "";
string MaDDH = dt.Rows[i]["Code_Type"].ToString();
string SoDDH = dt.Rows[i]["Code_No"].ToString();
string MaLSX = dt.Rows[i]["Production_Planning_Code"].ToString();
string SoLSX = dt.Rows[i]["Production_Planning_No"].ToString();
string codeSanPham = dt.Rows[i]["Product_Code"].ToString();
string MaVatLieu = dt.Rows[i]["Material_Code"].ToString();
double SoNVLCanLanh = (dt.Rows[i]["amount_of_material_receive"] != null && dt.Rows[i]["amount_of_material_receive"].ToString() != "") ? double.Parse(dt.Rows[i]["amount_of_material_receive"].ToString()) : 0;
double SoNVLTrongKho = (dt.Rows[i]["Avaiable_Material_Quanity"] != null && dt.Rows[i]["Avaiable_Material_Quanity"].ToString() != "") ? double.Parse(dt.Rows[i]["Avaiable_Material_Quanity"].ToString()) : 0;
sqlcheck = @"select COUNT(*) from t_OCTD where TD02 = '" + MaDDH + "' and TD03 ='" + SoDDH + "' and TD04='" + MaLSX + "' and TD05='" + SoLSX
+ "' and TD07 ='" + codeSanPham + "' and TD15 ='" + MaVatLieu + "'";
sqlCON check = new sqlCON();
if (int.Parse(check.sqlExecuteScalarString(sqlcheck)) == 0) //insert
{
string list = "";
for (int j = 0; j < dt.Columns.Count; j++)
{
list += "'";
list += dt.Rows[i][j].ToString() + "',";
}
StringBuilder sqlinsert = new StringBuilder();
sqlinsert.Append("insert into t_OCTD ");
sqlinsert.Append(@"(TD01,TD02,TD03,TD04,TD05,TD06,TD07,TD08,TD09,TD10,TD11,TD12,TD13,TD14,TD15,TD16,TD17,TD18,TD19,TD20,TD21,TD31,TD32,TD33,UserName,datetimeRST) values ( ");
sqlinsert.Append(list);
if (SoNVLTrongKho > SoNVLCanLanh)
{
sqlinsert.Append("'OK', 'OK' , '0' " + ",");
}
else if (SoNVLTrongKho == SoNVLCanLanh)
{
sqlinsert.Append("'OK', 'OK' , '1' " + ",");
}
else if (SoNVLTrongKho < SoNVLCanLanh)
{
sqlinsert.Append("'NG', 'NG' , '2' " + ",");
}
sqlinsert.Append("'" + Class.valiballecommon.GetStorage().UserName + "',GETDATE())");
sqlCON insert = new sqlCON();
insert.sqlExecuteNonQuery(sqlinsert.ToString(), false);
}
else //update
{
StringBuilder sqlupdate = new StringBuilder();
sqlupdate.Append("update t_OCTD set ");
sqlupdate.Append(@"TD18 = '" + dt.Rows[i]["amount_of_material_receive"].ToString() + "',");
sqlupdate.Append(@"TD18 = '" + dt.Rows[i]["amount_of_material_use"].ToString() + "',");
sqlupdate.Append(@"TD19 = '" + dt.Rows[i]["Avaiable_Material_Quanity"].ToString() + "',");
sqlupdate.Append(@"TD20 = '" + dt.Rows[i]["Production_Material_Quantity"].ToString() + "'");
if (SoNVLTrongKho > SoNVLCanLanh)
{
sqlupdate.Append(@", TD31 = 'OK' ,");
sqlupdate.Append(@" TD32 = 'OK' ,");
sqlupdate.Append(@"TD33 = '0', ");
}
else if (SoNVLTrongKho == SoNVLCanLanh)
{
sqlupdate.Append(@", TD31 = 'OK' ,");
sqlupdate.Append(@" TD32 = 'OK' ,");
sqlupdate.Append(@"TD33 = '1' ,");
}
else if (SoNVLTrongKho < SoNVLCanLanh)
{
sqlupdate.Append(@", TD31 = 'NG' ,");
sqlupdate.Append(@" TD32 = 'NG' ,");
sqlupdate.Append(@"TD33 = '2' ,");
}
sqlupdate.Append(@" UserName = '******' ,");
sqlupdate.Append(@" datetimeRST = GETDATE()");
sqlupdate.Append(@" where TD02 = '" + MaDDH + "' and TD03 ='" + SoDDH + "' and TD04='" + MaLSX + "' and TD05='" + SoLSX
+ "' and TD07 ='" + codeSanPham + "' and TD15 ='" + MaVatLieu + "'");
sqlCON update = new sqlCON();
update.sqlExecuteNonQuery(sqlupdate.ToString(), false);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Update or Insert to database fail: " + ex.Message, "Error");
}
}
}
public List <OrderVariable> LoadOrderInformationbyDatebyDept(DateTime from, DateTime to, string DeptCients)
{
List <OrderVariable> orderVariables = new List <OrderVariable>();
try
{
DataTable dt = new DataTable();
sqlERPCON sqlERPCON = new sqlERPCON();
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@"select smes.ME002 as Dept,TD001 + '-' + TD002 as DDH, TC012 as ClientOrder,TD004 as product ,TD005 as productName,
sum(TD008) as QuantityOfOrder, sum(TD009) as QuantityOfDelivery,TD010 as Unit, TD013 as ClientRequestDate from COPTD ptd
inner join COPTC ptc on ptc.TC001 = ptd.TD001 and ptc.TC002 = ptd.TD002
left join CMSME smes on smes.ME001 = ptc.TC005
where ptc.TC027 ='Y' and TD016 ='N'
");
if (DeptCients == "B01-MH")
{
stringBuilder.Append(" and TD001 like 'B%' and ( TD004 like '%BMH%' or TD004 like '%BWTX%') ");
}
else if (DeptCients == "B01-FF")
{
stringBuilder.Append(" and TD001 like 'B%' and ( TD004 like '%BFF%' ) ");
}
else if (DeptCients == "A01-Gia Dung")
{
stringBuilder.Append(" and TD001 like 'A%' ");
}
else if (DeptCients == "A01-PTC")
{
stringBuilder.Append(" and TD001 like 'P%' ");
}
else if (DeptCients == "A01-MH")
{
stringBuilder.Append("and TD001 like 'J%' and TD004 like 'JMH%' ");
}
else if (DeptCients == "A01-JM")
{
stringBuilder.Append(" and TD001 like 'J%' and TD004 like 'JM%' ");
}
stringBuilder.Append(" and CONVERT(date,ptd.TD013) >= '" + from.ToString("yyyyMMdd") + "' ");
stringBuilder.Append(" and CONVERT(date,ptd.TD013) <= '" + to.ToString("yyyyMMdd") + "' ");
stringBuilder.Append(@" group by TD001,TC012, TD002,TD005,TD010, TD013,smes.ME002,TD004
order by TD013 ");
sqlERPCON.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
foreach (DataRow dr in dt.Rows)
{
OrderVariable order = new OrderVariable();
order.Dept = (dr["Dept"] != null) ? dr["Dept"].ToString().Trim() : "";
order.DDH = (dr["DDH"] != null) ? dr["DDH"].ToString().Trim() : "";
order.ClientOrder = (dr["ClientOrder"] != null) ? dr["ClientOrder"].ToString().Trim() : "";
order.Unit = (dr["Unit"] != null) ? dr["Unit"].ToString().Trim() : "";
order.Product = (dr["product"] != null) ? dr["product"].ToString().Trim() : "";
order.ProductName = (dr["productName"] != null) ? dr["productName"].ToString().Trim() : "";
order.ClientOrderQty = (dr["QuantityOfOrder"] != null && dr["QuantityOfOrder"].ToString() != "") ? (double.Parse(dr["QuantityOfOrder"].ToString().Trim())) : 0;
order.DeliveryQty = (dr["QuantityOfDelivery"] != null && dr["QuantityOfDelivery"].ToString() != "") ? (double.Parse(dr["QuantityOfDelivery"].ToString().Trim())) : 0;
order.DeliveryOverQty = QuantityOfNotYetDelivery(order.DDH.Split('-')[0], order.DDH.Split('-')[1], order.Product, from);
// order.ClientOrderQty = order.ClientOrderQty - order.DeliveryOverQty - order.DeliveryQty;
order.ClientRequestDate = (dr["ClientRequestDate"] != null && dr["ClientRequestDate"].ToString() != "") ? DateTime.Parse(dr["ClientRequestDate"].ToString().Trim().Insert(4, "-").Insert(7, "-")) : DateTime.MinValue;
orderVariables.Add(order);
}
}
catch (Exception)
{
return(null);
}
return(orderVariables);
}