public void ApGia() { string blvtid; string sql = "select systableid from systable where TableName='BLVT' and syspackageid=" + Config.GetValue("sysPackageID").ToString(); blvtid = _dbStruct.GetValue(sql).ToString(); sql = "select sysfieldid from sysfield where systableid=" + blvtid + " and FieldName='Dongia'"; string dongiaID = _dbStruct.GetValue(sql).ToString(); sql = "select sysfieldid from sysfield where systableid=" + blvtid + " and FieldName='Soluong_x'"; string Soluong_xID = _dbStruct.GetValue(sql).ToString(); sql = "select sysfieldid from sysfield where systableid=" + blvtid + " and FieldName='Soluong'"; string Soluong_nID = _dbStruct.GetValue(sql).ToString(); sql = "select b.mttableid,b.dttableid,a.* from sysdataconfigdt a,sysdataconfig b "; sql += "where b.blconfigid=a.blconfigid "; sql += "and systableid=" + blvtid + " AND blfieldid=" + dongiaID; sql += " AND (a.blconfigid in(select blconfigid from sysdataconfigdt where blfieldid=" + Soluong_xID + " AND (mtFieldID IS NOT NULL OR dtFieldID IS NOT NULL OR Formula IS NOT NULL)) or NhomDK='HTL1' ) and NhomDK<>'PNH5'";//or NhomDK='PNH1' DataTable dsMtdt = _dbStruct.GetDataTable(sql); string mt; string dt; string GiaField = dongiaID; // string tygiaField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='TyGia'").ToString(); //trong blvt là 1795 - rồi tìm trong bảng nguồn tương ứng string GiaNTField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='DonGiaNT'").ToString(); //trong blvt là 1792 - string mavtField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='MaVT'").ToString(); //trong blvt là 1796 - rồi tìm trong bảng nguồn tương ứng string makhoField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='MaKho'").ToString(); //trong blvt là 1797 - rồi tìm trong bảng nguồn tương ứng string ngayctField = _dbStruct.GetValue("select sysfieldid from sysfield where sysTableid=" + blvtid + " and FieldName='NgayCt'").ToString(); //trong blvt là 1782 - rồi tìm trong bảng nguồn tương ứng updateCongthuc UpdateGia; PostBl post; //bool mtdt = false; _dbData.BeginMultiTrans(); try { foreach (DataRow dr in dsMtdt.Rows)//duyệt qua từ loại chứng từ { mt = _dbStruct.GetValue("select tableName from systable where systableid=" + dr["mttableid"].ToString()).ToString(); if (dr["dttableid"] != DBNull.Value) { dt = _dbStruct.GetValue("select tableName from systable where systableid=" + dr["dttableid"].ToString()).ToString(); } else { continue; } string t = mt + ": "; TimeSpan ts; DateTime d1 = System.DateTime.Now; string GiaFieldName; string tygiaFieldName = ""; string GiaNTFieldName; string mavtFieldName; string makhoFieldName; string ngayctFieldName; if (!(dr["dtfieldid"] is DBNull)) {//Lấy thông tin các trường cần dùng GiaFieldName = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=" + GiaField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString(); GiaFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + GiaFieldName).ToString(); mavtFieldName = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=" + mavtField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString(); mavtFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + mavtFieldName).ToString(); //xét trường hợp mã kho nằm trong dt hay mt bool khoinMt = false; makhoFieldName = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=" + makhoField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString(); if (makhoFieldName == null || makhoFieldName == "") { makhoFieldName = _dbStruct.GetValue(" select mtfieldid from sysdataconfigdt where blfieldid=" + makhoField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString(); khoinMt = true; } makhoFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + makhoFieldName).ToString(); ngayctFieldName = _dbStruct.GetValue(" select mtfieldid from sysdataconfigdt where blfieldid=" + ngayctField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString(); ngayctFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + ngayctFieldName).ToString(); GiaNTFieldName = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=" + GiaNTField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString(); GiaNTFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + GiaNTFieldName).ToString(); tygiaFieldName = _dbStruct.GetValue(" select mtfieldid from sysdataconfigdt where blfieldid=" + tygiaField + " and blconfigid=" + dr["blconfigid"].ToString()).ToString(); if (tygiaFieldName != "") { tygiaFieldName = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + tygiaFieldName).ToString(); } //foreach (DataRow rGia in _dtVatTu.Rows)//duyệt qua bảng giá //{ // updateMTDT(mt, dt, GiaFieldName, GiaNTFieldName, tygiaFieldName, rGia, mavtFieldName, makhoFieldName, ngayctFieldName); //} ts = DateTime.Now - d1; t += ts.TotalMilliseconds.ToString("### ### ###.##") + "--khoitao;"; updateMTDT(mt, dt, GiaFieldName, GiaNTFieldName, tygiaFieldName, mavtFieldName, makhoFieldName, ngayctFieldName, khoinMt); ts = DateTime.Now - d1; t += ts.TotalMilliseconds.ToString("### ### ###.##") + "--updateMT;"; d1 = DateTime.Now; UpdateGia = new updateCongthuc(_dbData, dr["mttableid"].ToString(), dr["dttableid"].ToString(), GiaNTFieldName, TuNgay, DenNgay, ngayctFieldName); UpdateGia.Update(); ts = DateTime.Now - d1; t += ts.TotalMilliseconds.ToString("### ### ###.##") + "--updateGia"; d1 = DateTime.Now; post = new PostBl(_dbData, dr["mttableid"].ToString(), TuNgay, DenNgay, UpdateGia.LField); if (dr["mttableid"].ToString() == "2560") { } post.Post(); ts = DateTime.Now - d1; t += ts.TotalMilliseconds.ToString("### ### ###.##") + "--Post"; // MessageBox.Show(t); } if (_dbData.HasErrors) { _dbData.RollbackMultiTrans(); break; } } if (!_dbData.HasErrors) { _dbData.EndMultiTrans(); } } catch (SqlException ex) { MessageBox.Show(ex.Message); _dbData.RollbackMultiTrans(); } }
public void Apgia() { string sql = "select b.mttableid,b.dttableid,a.* from sysdataconfigdt a,sysdataconfig b "; sql += "where b.blconfigid=a.blconfigid "; sql += "and systableid=330 AND blfieldid=3484 "; //330: mã của bảng blvt, 3485: mã trường blvt.DonGiaNT sql += "AND b.dttableid is not null and (a.blconfigid in(select blconfigid from sysdataconfigdt where blfieldid=3484 AND (mtFieldID IS NOT NULL OR dtFieldID IS NOT NULL OR Formula IS NOT NULL)) or NhomDK='HTL1' )"; DataTable dsMtdt = _dbStruct.GetDataTable(sql); string mt; string dt; string GiaField; string tygiaField; //trong blvt là 3480 - rồi tìm trong bảng nguồn tương ứng string GiaNTField; //trong blvt là 3485 - //string mavtField;//trong blvt là 1796 - rồi tìm trong bảng nguồn tương ứng //string makhoField;//trong blvt là 1797 - rồi tìm trong bảng nguồn tương ứng string ngayctField; //trong blvt là 3467 - rồi tìm trong bảng nguồn tương ứng string MaCT; string MTIDDT; //trong blvt là 3487 - rồi tìm trong bảng nguồn tương ứng updateCongthuc UpdateGia; PostBl post; //bool mtdt = false; _dbData.BeginMultiTrans(); try { foreach (DataRow dr in dsMtdt.Rows)//duyệt qua từ loại chứng từ { mt = _dbStruct.GetValue("select tableName from systable where systableid=" + dr["mttableid"].ToString()).ToString(); dt = _dbStruct.GetValue("select tableName from systable where systableid=" + dr["dttableid"].ToString()).ToString(); MaCT = _dbStruct.GetValue("select MaCT from systable where systableid=" + dr["dttableid"].ToString()).ToString(); if (!(dr["dtfieldid"] is DBNull)) {//Lấy thông tin các trường cần dùng GiaField = _dbStruct.GetValue(" select fieldName from sysfield where systableid=" + dr["dttableid"].ToString() + " and sysfieldid=" + dr["dtfieldid"].ToString()).ToString(); GiaNTField = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=3485 and blconfigid=" + dr["blconfigid"].ToString()).ToString(); GiaNTField = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + GiaNTField).ToString(); MTIDDT = _dbStruct.GetValue(" select dtfieldid from sysdataconfigdt where blfieldid=3487 and blconfigid=" + dr["blconfigid"].ToString()).ToString(); MTIDDT = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + MTIDDT).ToString(); ngayctField = _dbStruct.GetValue(" select mtfieldid from sysdataconfigdt where blfieldid=3467 and blconfigid=" + dr["blconfigid"].ToString()).ToString(); ngayctField = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + ngayctField).ToString(); tygiaField = _dbStruct.GetValue(" select mtfieldid from sysdataconfigdt where blfieldid=3480 and blconfigid=" + dr["blconfigid"].ToString()).ToString(); if (tygiaField != "") { tygiaField = _dbStruct.GetValue(" select fieldName from sysfield where sysfieldid=" + tygiaField).ToString(); } foreach (DataRow rGia in _dtVatTu.Select("MaCT='" + MaCT + "'"))//duyệt qua bảng giá tương ứng với bảng dữ liệu { if (rGia["DonGia"] != DBNull.Value || rGia["DonGia"].ToString() != string.Empty) { updateMTDT(mt, dt, GiaField, GiaNTField, tygiaField, rGia, MTIDDT); } } UpdateGia = new updateCongthuc(_dbData, dr["mttableid"].ToString(), dr["dttableid"].ToString(), GiaNTField, TuNgay, DenNgay, ngayctField); UpdateGia.Update(); post = new PostBl(_dbData, dr["mttableid"].ToString(), TuNgay, DenNgay, UpdateGia.LField); post.Post(); } if (_dbData.HasErrors) { _dbData.RollbackMultiTrans(); break; } } if (!_dbData.HasErrors) { _dbData.EndMultiTrans(); } } catch (SqlException ex) { if (_dbData.Connection.State != ConnectionState.Closed) { _dbData.Connection.Close(); } MessageBox.Show(ex.Message); } }