} // prepare /// <summary> /// Perrform Process. /// </summary> /// <returns>clear message</returns> protected override String DoIt() { StringBuilder sql = null; int no = 0; String clientCheck = " AND AD_Client_ID=" + _AD_Client_ID; // **** Prepare **** // Delete Old Imported if (_deleteOldImported) { sql = new StringBuilder("DELETE FROM I_Invoice " + "WHERE I_IsImported='Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Delete Old Impored =" + no); } // Set Client, Org, IsActive, Created/Updated sql = new StringBuilder("UPDATE I_Invoice " + "SET AD_Client_ID = COALESCE (AD_Client_ID,").Append(_AD_Client_ID).Append(")," + " AD_Org_ID = COALESCE (AD_Org_ID,").Append(_AD_Org_ID).Append(")," + " IsActive = COALESCE (IsActive, 'Y')," + " Created = COALESCE (Created, SysDate)," + " CreatedBy = COALESCE (CreatedBy, 0)," + " Updated = COALESCE (Updated, SysDate)," + " UpdatedBy = COALESCE (UpdatedBy, 0)," + " I_ErrorMsg = NULL," + " I_IsImported = 'N' " + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL"); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Info("Reset=" + no); String ts = DataBase.DB.IsPostgreSQL() ? "COALESCE(I_ErrorMsg,'')" : "I_ErrorMsg"; //java bug, it could not be used directly sql = new StringBuilder("UPDATE I_Invoice o " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Org, '" + "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0" + " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Org=" + no); } // Document Type - PO - SO sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName" + " AND d.DocBaseType IN ('API','APC') AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx='N' AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set PO DocType=" + no); } sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName" + " AND d.DocBaseType IN ('ARI','ARC') AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx='Y' AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set SO DocType=" + no); } sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName" + " AND d.DocBaseType IN ('API','ARI','APC','ARC') AND o.AD_Client_ID=d.AD_Client_ID) " //+ "WHERE C_DocType_ID IS NULL AND IsSOTrx IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append (clientCheck); + "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set DocType=" + no); } sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid DocTypeName, ' " + "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid DocTypeName=" + no); } // DocType Default sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'" + " AND d.DocBaseType='API' AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx='N' AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set PO Default DocType=" + no); } sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'" + " AND d.DocBaseType='ARI' AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx='Y' AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set SO Default DocType=" + no); } sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'" + " AND d.DocBaseType IN('ARI','API') AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx IS NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set Default DocType=" + no); } sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No DocType, ' " + "WHERE C_DocType_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No DocType=" + no); } // Set IsSOTrx sql = new StringBuilder("UPDATE I_Invoice o SET IsSOTrx='Y' " + "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='ARI' AND o.AD_Client_ID=d.AD_Client_ID)" + " AND C_DocType_ID IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set IsSOTrx=Y=" + no); sql = new StringBuilder("UPDATE I_Invoice o SET IsSOTrx='N' " + "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='API' AND o.AD_Client_ID=d.AD_Client_ID)" + " AND C_DocType_ID IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set IsSOTrx=N=" + no); // Price List sql = new StringBuilder("UPDATE I_Invoice o " + "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p WHERE p.IsDefault='Y'" + " AND p.C_Currency_ID=o.C_Currency_ID AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_PriceList_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Default Currency PriceList=" + no); sql = new StringBuilder("UPDATE I_Invoice o " + "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p WHERE p.IsDefault='Y'" + " AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_PriceList_ID IS NULL AND C_Currency_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Default PriceList=" + no); sql = new StringBuilder("UPDATE I_Invoice o " + "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p " + " WHERE p.C_Currency_ID=o.C_Currency_ID AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_PriceList_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Currency PriceList=" + no); sql = new StringBuilder("UPDATE I_Invoice o " + "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p " + " WHERE p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_PriceList_ID IS NULL AND C_Currency_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set PriceList=" + no); // sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No PriceList, ' " + "WHERE M_PriceList_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No PriceList=" + no); } // Payment Rule // We support Payment Rule being input in the login language VAdvantage.Login.Language language = VAdvantage.Login.Language.GetLoginLanguage(); // Base Language String AD_Language = language.GetAD_Language(); sql = new StringBuilder("UPDATE I_Invoice O " + "SET PaymentRule= " + "(SELECT R.Value " + " FROM AD_Ref_List R " + " left outer join AD_Ref_List_Trl RT " + " on RT.AD_Ref_List_ID = R.AD_Ref_List_ID and RT.AD_Language = @param " + " WHERE R.AD_Reference_ID = 195 and coalesce( RT.Name, R.Name ) = O.PaymentRuleName ) " + "WHERE PaymentRule is null AND PaymentRuleName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck); SqlParameter[] param = new SqlParameter[1]; param[0] = new SqlParameter("@param", AD_Language); no = DataBase.DB.ExecuteQuery(sql.ToString(), param, Get_TrxName()); log.Fine("Set PaymentRule=" + no); // do not set a default; if null, the import logic will derive from the business partner // do not error in absence of a default // Payment Term sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_PaymentTerm_ID=(SELECT C_PaymentTerm_ID FROM C_PaymentTerm p" + " WHERE o.PaymentTermValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE C_PaymentTerm_ID IS NULL AND PaymentTermValue IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set PaymentTerm=" + no); sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_PaymentTerm_ID=(SELECT MAX(C_PaymentTerm_ID) FROM C_PaymentTerm p" + " WHERE p.IsDefault='Y' AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE C_PaymentTerm_ID IS NULL AND o.PaymentTermValue IS NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Default PaymentTerm=" + no); // sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No PaymentTerm, ' " + "WHERE C_PaymentTerm_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No PaymentTerm=" + no); } // BP from EMail sql = new StringBuilder("UPDATE I_Invoice o " + "SET (C_BPartner_ID,AD_User_ID)=(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u" + " WHERE o.EMail=u.EMail AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL) " + "WHERE C_BPartner_ID IS NULL AND EMail IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set BP from EMail=" + no); // BP from ContactName sql = new StringBuilder("UPDATE I_Invoice o " + "SET (C_BPartner_ID,AD_User_ID)=(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u" + " WHERE o.ContactName=u.Name AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL) " + "WHERE C_BPartner_ID IS NULL AND ContactName IS NOT NULL" + " AND EXISTS (SELECT Name FROM AD_User u WHERE o.ContactName=u.Name AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL GROUP BY Name HAVING COUNT(*)=1)" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set BP from ContactName=" + no); // BP from Value sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_BPartner_ID=(SELECT MAX(C_BPartner_ID) FROM C_BPartner bp" + " WHERE o.BPartnerValue=bp.Value AND o.AD_Client_ID=bp.AD_Client_ID) " + "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set BP from Value=" + no); // Default BP sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_BPartner_ID=(SELECT C_BPartnerCashTrx_ID FROM AD_ClientInfo c" + " WHERE o.AD_Client_ID=c.AD_Client_ID) " + "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NULL AND Name IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Default BP=" + no); // Existing Location ? Exact Match sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_BPartner_Location_ID=(SELECT C_BPartner_Location_ID" + " FROM C_BPartner_Location bpl INNER JOIN C_Location l ON (bpl.C_Location_ID=l.C_Location_ID)" + " WHERE o.C_BPartner_ID=bpl.C_BPartner_ID AND bpl.AD_Client_ID=o.AD_Client_ID" + " AND DUMP(o.Address1)=DUMP(l.Address1) AND DUMP(o.Address2)=DUMP(l.Address2)" + " AND DUMP(o.City)=DUMP(l.City) AND DUMP(o.Postal)=DUMP(l.Postal)" + " AND DUMP(o.C_Region_ID)=DUMP(l.C_Region_ID) AND DUMP(o.C_Country_ID)=DUMP(l.C_Country_ID)) " + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL" + " AND I_IsImported='N'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Found Location=" + no); // Set Location from BPartner sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_BPartner_Location_ID=(SELECT MAX(C_BPartner_Location_ID) FROM C_BPartner_Location l" + " WHERE l.C_BPartner_ID=o.C_BPartner_ID AND o.AD_Client_ID=l.AD_Client_ID" + " AND ((l.IsBillTo='Y' AND o.IsSOTrx='Y') OR o.IsSOTrx='N')" + ") " + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set BP Location from BP=" + no); // sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No BP Location, ' " + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No BP Location=" + no); } // Set Country /** * sql = new StringBuilder ("UPDATE I_Invoice o " + "SET CountryCode=(SELECT CountryCode FROM C_Country c WHERE c.IsDefault='Y'" + " AND c.AD_Client_ID IN (0, o.AD_Client_ID) AND ROWNUM=1) " + "WHERE C_BPartner_ID IS NULL AND CountryCode IS NULL AND C_Country_ID IS NULL" + " AND I_IsImported<>'Y'").Append (clientCheck); + no = DataBase.DB.ExecuteQuery(sql.ToString(),null, Get_TrxName()); + log.Fine("Set Country Default=" + no); **/ sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_Country_ID=(SELECT C_Country_ID FROM C_Country c" + " WHERE o.CountryCode=c.CountryCode AND c.IsSummary='N' AND c.AD_Client_ID IN (0, o.AD_Client_ID)) " + "WHERE C_BPartner_ID IS NULL AND C_Country_ID IS NULL AND CountryCode IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Country=" + no); // sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Country, ' " + "WHERE C_BPartner_ID IS NULL AND C_Country_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Country=" + no); } // Set Region sql = new StringBuilder("UPDATE I_Invoice o " + "Set RegionName=(SELECT MAX(Name) FROM C_Region r" + " WHERE r.IsDefault='Y' AND r.C_Country_ID=o.C_Country_ID" + " AND r.AD_Client_ID IN (0, o.AD_Client_ID)) " + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL AND RegionName IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Region Default=" + no); // sql = new StringBuilder("UPDATE I_Invoice o " + "Set C_Region_ID=(SELECT C_Region_ID FROM C_Region r" + " WHERE r.Name=o.RegionName AND r.C_Country_ID=o.C_Country_ID" + " AND r.AD_Client_ID IN (0, o.AD_Client_ID)) " + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL AND RegionName IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Region=" + no); // sql = new StringBuilder("UPDATE I_Invoice o " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Region, ' " + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL " + " AND EXISTS (SELECT * FROM C_Country c" + " WHERE c.C_Country_ID=o.C_Country_ID AND c.HasRegion='Y')" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Region=" + no); } // Product sql = new StringBuilder("UPDATE I_Invoice o " + "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p" + " WHERE o.ProductValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL AND ProductValue IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Product from Value=" + no); sql = new StringBuilder("UPDATE I_Invoice o " + "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p" + " WHERE o.UPC=p.UPC AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL AND UPC IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Product from UPC=" + no); sql = new StringBuilder("UPDATE I_Invoice o " + "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p" + " WHERE o.SKU=p.SKU AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL AND SKU IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Product fom SKU=" + no); sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Product, ' " + "WHERE M_Product_ID IS NULL AND (ProductValue IS NOT NULL OR UPC IS NOT NULL OR SKU IS NOT NULL)" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Product=" + no); } // Tax sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_Tax_ID=(SELECT MAX(C_Tax_ID) FROM C_Tax t" + " WHERE o.TaxIndicator=t.TaxIndicator AND o.AD_Client_ID=t.AD_Client_ID) " + "WHERE C_Tax_ID IS NULL AND TaxIndicator IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Tax=" + no); sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Tax, ' " + "WHERE C_Tax_ID IS NULL AND TaxIndicator IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Tax=" + no); } Commit(); // -- New BPartner --------------------------------------------------- // Go through Invoice Records w/o C_BPartner_ID sql = new StringBuilder("SELECT * FROM I_Invoice " + "WHERE I_IsImported='N' AND C_BPartner_ID IS NULL").Append(clientCheck); IDataReader idr = null; try { //PreparedStatement pstmt = DataBase.prepareStatement (sql.ToString(), Get_TrxName()); idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName()); while (idr.Read()) { X_I_Invoice imp = new X_I_Invoice(GetCtx(), idr, Get_TrxName()); if (imp.GetBPartnerValue() == null) { if (imp.GetEMail() != null) { imp.SetBPartnerValue(imp.GetEMail()); } else if (imp.GetName() != null) { imp.SetBPartnerValue(imp.GetName()); } else { continue; } } if (imp.GetName() == null) { if (imp.GetContactName() != null) { imp.SetName(imp.GetContactName()); } else { imp.SetName(imp.GetBPartnerValue()); } } // BPartner MBPartner bp = MBPartner.Get(GetCtx(), imp.GetBPartnerValue()); if (bp == null) { bp = new MBPartner(GetCtx(), -1, Get_TrxName()); bp.SetClientOrg(imp.GetAD_Client_ID(), imp.GetAD_Org_ID()); bp.SetValue(imp.GetBPartnerValue()); bp.SetName(imp.GetName()); if (!bp.Save()) { continue; } } imp.SetC_BPartner_ID(bp.GetC_BPartner_ID()); // BP Location MBPartnerLocation bpl = null; MBPartnerLocation[] bpls = bp.GetLocations(true); for (int i = 0; bpl == null && i < bpls.Length; i++) { if (imp.GetC_BPartner_Location_ID() == bpls[i].GetC_BPartner_Location_ID()) { bpl = bpls[i]; } // Same Location ID else if (imp.GetC_Location_ID() == bpls[i].GetC_Location_ID()) { bpl = bpls[i]; } // Same Location Info else if (imp.GetC_Location_ID() == 0) { MLocation loc = bpl.GetLocation(false); if (loc.Equals(imp.GetC_Country_ID(), imp.GetC_Region_ID(), imp.GetPostal(), "", imp.GetCity(), imp.GetAddress1(), imp.GetAddress2())) { bpl = bpls[i]; } } } if (bpl == null) { // New Location MLocation loc = new MLocation(GetCtx(), 0, Get_TrxName()); loc.SetAddress1(imp.GetAddress1()); loc.SetAddress2(imp.GetAddress2()); loc.SetCity(imp.GetCity()); loc.SetPostal(imp.GetPostal()); if (imp.GetC_Region_ID() != 0) { loc.SetC_Region_ID(imp.GetC_Region_ID()); } loc.SetC_Country_ID(imp.GetC_Country_ID()); if (!loc.Save()) { continue; } // bpl = new MBPartnerLocation(bp); bpl.SetC_Location_ID(imp.GetC_Location_ID()); if (!bpl.Save()) { continue; } } imp.SetC_Location_ID(bpl.GetC_Location_ID()); imp.SetC_BPartner_Location_ID(bpl.GetC_BPartner_Location_ID()); // User/Contact if (imp.GetContactName() != null || imp.GetEMail() != null || imp.GetPhone() != null) { MUser[] users = bp.GetContacts(true); MUser user = null; for (int i = 0; user == null && i < users.Length; i++) { String name = users[i].GetName(); if (name.Equals(imp.GetContactName()) || name.Equals(imp.GetName())) { user = users[i]; imp.SetAD_User_ID(user.GetAD_User_ID()); } } if (user == null) { user = new MUser(bp); if (imp.GetContactName() == null) { user.SetName(imp.GetName()); } else { user.SetName(imp.GetContactName()); } user.SetEMail(imp.GetEMail()); user.SetPhone(imp.GetPhone()); if (user.Save()) { imp.SetAD_User_ID(user.GetAD_User_ID()); } } } imp.Save(); } // for all new BPartners idr.Close(); // } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, "CreateBP", e); } sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No BPartner, ' " + "WHERE C_BPartner_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No BPartner=" + no); } Commit(); // -- New Invoices ----------------------------------------------------- int noInsert = 0; int noInsertLine = 0; // Go through Invoice Records w/o sql = new StringBuilder("SELECT * FROM I_Invoice " + "WHERE I_IsImported='N'").Append(clientCheck) .Append(" ORDER BY C_BPartner_ID, C_BPartner_Location_ID, I_Invoice_ID"); try { //PreparedStatement pstmt = DataBase.prepareStatement (sql.ToString(), Get_TrxName()); idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName()); // Group Change int oldC_BPartner_ID = 0; int oldC_BPartner_Location_ID = 0; String oldDocumentNo = ""; // MInvoice invoice = null; int lineNo = 0; while (idr.Read()) { X_I_Invoice imp = new X_I_Invoice(GetCtx(), idr, null); String cmpDocumentNo = imp.GetDocumentNo(); if (cmpDocumentNo == null) { cmpDocumentNo = ""; } // New Invoice if (oldC_BPartner_ID != imp.GetC_BPartner_ID() || oldC_BPartner_Location_ID != imp.GetC_BPartner_Location_ID() || !oldDocumentNo.Equals(cmpDocumentNo)) { if (invoice != null) { invoice.ProcessIt(_docAction); invoice.Save(); } // Group Change oldC_BPartner_ID = imp.GetC_BPartner_ID(); oldC_BPartner_Location_ID = imp.GetC_BPartner_Location_ID(); oldDocumentNo = imp.GetDocumentNo(); if (oldDocumentNo == null) { oldDocumentNo = ""; } // invoice = new MInvoice(GetCtx(), 0, null); invoice.SetClientOrg(imp.GetAD_Client_ID(), imp.GetAD_Org_ID()); invoice.SetC_DocTypeTarget_ID(imp.GetC_DocType_ID(), true); if (imp.GetDocumentNo() != null) { invoice.SetDocumentNo(imp.GetDocumentNo()); } // invoice.SetC_BPartner_ID(imp.GetC_BPartner_ID()); invoice.SetC_BPartner_Location_ID(imp.GetC_BPartner_Location_ID()); if (imp.GetAD_User_ID() != 0) { invoice.SetAD_User_ID(imp.GetAD_User_ID()); } // if (imp.GetDescription() != null) { invoice.SetDescription(imp.GetDescription()); } if (imp.GetPaymentRule() != null) { invoice.SetPaymentRule(imp.GetPaymentRule()); } invoice.SetC_PaymentTerm_ID(imp.GetC_PaymentTerm_ID()); invoice.SetM_PriceList_ID(imp.GetM_PriceList_ID()); MPriceList pl = MPriceList.Get(GetCtx(), imp.GetM_PriceList_ID(), Get_TrxName()); invoice.SetIsTaxIncluded(pl.IsTaxIncluded()); // SalesRep from Import or the person running the import if (imp.GetSalesRep_ID() != 0) { invoice.SetSalesRep_ID(imp.GetSalesRep_ID()); } if (invoice.GetSalesRep_ID() == 0) { invoice.SetSalesRep_ID(GetAD_User_ID()); } // if (imp.GetAD_OrgTrx_ID() != 0) { invoice.SetAD_OrgTrx_ID(imp.GetAD_OrgTrx_ID()); } if (imp.GetC_Activity_ID() != 0) { invoice.SetC_Activity_ID(imp.GetC_Activity_ID()); } if (imp.GetC_Campaign_ID() != 0) { invoice.SetC_Campaign_ID(imp.GetC_Campaign_ID()); } if (imp.GetC_Project_ID() != 0) { invoice.SetC_Project_ID(imp.GetC_Project_ID()); } // if (imp.GetDateInvoiced() != null) { invoice.SetDateInvoiced(imp.GetDateInvoiced()); } if (imp.GetDateAcct() != null) { invoice.SetDateAcct(imp.GetDateAcct()); } // invoice.Save(); noInsert++; lineNo = 10; } imp.SetC_Invoice_ID(invoice.GetC_Invoice_ID()); // New InvoiceLine MInvoiceLine line = new MInvoiceLine(invoice); if (imp.GetLineDescription() != null) { line.SetDescription(imp.GetLineDescription()); } line.SetLine(lineNo); lineNo += 10; if (imp.GetM_Product_ID() != 0) { line.SetM_Product_ID(imp.GetM_Product_ID(), true); } line.SetQty(imp.GetQtyOrdered()); line.SetPrice(); Decimal?price = (Decimal?)imp.GetPriceActual(); if (price != null && Env.ZERO.CompareTo(price) != 0) { line.SetPrice(price.Value); } if (imp.GetC_Tax_ID() != 0) { line.SetC_Tax_ID(imp.GetC_Tax_ID()); } else { line.SetTax(); imp.SetC_Tax_ID(line.GetC_Tax_ID()); } Decimal?taxAmt = (Decimal?)imp.GetTaxAmt(); if (taxAmt != null && Env.ZERO.CompareTo(taxAmt) != 0) { line.SetTaxAmt(taxAmt); } line.Save(); // imp.SetC_InvoiceLine_ID(line.GetC_InvoiceLine_ID()); imp.SetI_IsImported(X_I_Invoice.I_ISIMPORTED_Yes); imp.SetProcessed(true); // if (imp.Save()) { noInsertLine++; } } if (invoice != null) { invoice.ProcessIt(_docAction); invoice.Save(); } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, "CreateInvoice", e); } // Set Error to indicator to not imported sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='N', Updated=SysDate " + "WHERE I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); AddLog(0, null, Utility.Util.GetValueOfDecimal(no), "@Errors@"); // AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsert), "@C_Invoice_ID@: @Inserted@"); AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsertLine), "@C_InvoiceLine_ID@: @Inserted@"); return(""); } // doIt
} // prepare /// <summary> /// Perrform Process. /// </summary> /// <returns>message</returns> protected override String DoIt() { StringBuilder sql = null; int no = 0; String clientCheck = " AND AD_Client_ID=" + _AD_Client_ID; // **** Prepare **** // Delete Old Imported if (_deleteOldImported) { sql = new StringBuilder("DELETE FROM I_BPartner " + "WHERE I_IsImported='Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Delete Old Impored =" + no); } // Set Client, Org, IsActive, Created/Updated sql = new StringBuilder("UPDATE I_BPartner " + "SET AD_Client_ID = COALESCE (AD_Client_ID, ").Append(_AD_Client_ID).Append(")," + " AD_Org_ID = COALESCE (AD_Org_ID, 0)," + " IsActive = COALESCE (IsActive, 'Y')," + " Created = COALESCE (Created, SysDate)," + " CreatedBy = COALESCE (CreatedBy, 0)," + " Updated = COALESCE (Updated, SysDate)," + " UpdatedBy = COALESCE (UpdatedBy, 0)," + " I_ErrorMsg = NULL," + " I_IsImported = 'N' " + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL"); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Reset=" + no); // Set BP_Group sql = new StringBuilder("UPDATE I_BPartner i " + "SET GroupValue=(SELECT MAX(Value) FROM C_BP_Group g WHERE g.IsDefault='Y'" + " AND g.AD_Client_ID=i.AD_Client_ID) "); sql.Append("WHERE GroupValue IS NULL AND C_BP_Group_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Group Default=" + no); // sql = new StringBuilder("UPDATE I_BPartner i " + "SET C_BP_Group_ID=(SELECT C_BP_Group_ID FROM C_BP_Group g" + " WHERE i.GroupValue=g.Value AND g.AD_Client_ID=i.AD_Client_ID) " + "WHERE C_BP_Group_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Group=" + no); // String ts = DataBase.DB.IsPostgreSQL() ? "COALESCE(I_ErrorMsg,'')" : "I_ErrorMsg"; //java bug, it could not be used directly sql = new StringBuilder("UPDATE I_BPartner " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Group, ' " + "WHERE C_BP_Group_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Config("Invalid Group=" + no); // Set Country /** * sql = new StringBuilder ("UPDATE I_BPartner i " + "SET CountryCode=(SELECT CountryCode FROM C_Country c WHERE c.IsDefault='Y'" + " AND c.AD_Client_ID IN (0, i.AD_Client_ID) AND ROWNUM=1) " + "WHERE CountryCode IS NULL AND C_Country_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); + no = DataBase.DB.ExecuteQuery(sql.ToString(),null, Get_TrxName()); + log.Fine("Set Country Default=" + no); **/ // sql = new StringBuilder("UPDATE I_BPartner i " + "SET C_Country_ID=(SELECT C_Country_ID FROM C_Country c" + " WHERE i.CountryCode=c.CountryCode AND c.IsSummary='N' AND c.AD_Client_ID IN (0, i.AD_Client_ID)) " + "WHERE C_Country_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Country=" + no); // sql = new StringBuilder("UPDATE I_BPartner " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Country, ' " + "WHERE C_Country_ID IS NULL AND (City IS NOT NULL OR Address1 IS NOT NULL)" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Config("Invalid Country=" + no); // Set Region sql = new StringBuilder("UPDATE I_BPartner i " + "Set RegionName=(SELECT Name FROM C_Region r" + " WHERE r.IsDefault='Y' AND r.C_Country_ID=i.C_Country_ID" + " AND r.AD_Client_ID IN (0, i.AD_Client_ID)) "); /* * if (DataBase.isOracle()) //jz * { * sql.Append(" AND ROWNUM=1) "); * } * else * sql.Append(" AND r.UPDATED IN (SELECT MAX(UPDATED) FROM C_Region r1" + " WHERE r1.IsDefault='Y' AND r1.C_Country_ID=i.C_Country_ID" + " AND r1.AD_Client_ID IN (0, i.AD_Client_ID) "); */ sql.Append("WHERE RegionName IS NULL AND C_Region_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Region Default=" + no); // sql = new StringBuilder("UPDATE I_BPartner i " + "Set C_Region_ID=(SELECT C_Region_ID FROM C_Region r" + " WHERE r.Name=i.RegionName AND r.C_Country_ID=i.C_Country_ID" + " AND r.AD_Client_ID IN (0, i.AD_Client_ID)) " + "WHERE C_Region_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Region=" + no); // sql = new StringBuilder("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Region, ' " + "WHERE C_Region_ID IS NULL " + " AND EXISTS (SELECT * FROM C_Country c" + " WHERE c.C_Country_ID=i.C_Country_ID AND c.HasRegion='Y')" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Config("Invalid Region=" + no); // Set Greeting sql = new StringBuilder("UPDATE I_BPartner i " + "SET C_Greeting_ID=(SELECT C_Greeting_ID FROM C_Greeting g" + " WHERE i.BPContactGreeting=g.Name AND g.AD_Client_ID IN (0, i.AD_Client_ID)) " + "WHERE C_Greeting_ID IS NULL AND BPContactGreeting IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Greeting=" + no); // sql = new StringBuilder("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Greeting, ' " + "WHERE C_Greeting_ID IS NULL AND BPContactGreeting IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Config("Invalid Greeting=" + no); // Existing User ? sql = new StringBuilder("UPDATE I_BPartner i " + "SET (C_BPartner_ID,AD_User_ID)=" + "(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u " + "WHERE i.EMail=u.EMail AND u.AD_Client_ID=i.AD_Client_ID) " + "WHERE i.EMail IS NOT NULL AND I_IsImported='N'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Found EMail User="******"UPDATE I_BPartner i " + "SET C_BPartner_ID=(SELECT C_BPartner_ID FROM C_BPartner p" + " WHERE i.Value=p.Value AND p.AD_Client_ID=i.AD_Client_ID) " + "WHERE C_BPartner_ID IS NULL AND Value IS NOT NULL" + " AND I_IsImported='N'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Found BPartner=" + no); // Existing Contact ? Match Name sql = new StringBuilder("UPDATE I_BPartner i " + "SET AD_User_ID=(SELECT AD_User_ID FROM AD_User c" + " WHERE i.ContactName=c.Name AND i.C_BPartner_ID=c.C_BPartner_ID AND c.AD_Client_ID=i.AD_Client_ID) " + "WHERE C_BPartner_ID IS NOT NULL AND AD_User_ID IS NULL AND ContactName IS NOT NULL" + " AND I_IsImported='N'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Found Contact=" + no); // Existing Location ? Exact Match sql = new StringBuilder("UPDATE I_BPartner i " + "SET C_BPartner_Location_ID=(SELECT C_BPartner_Location_ID" + " FROM C_BPartner_Location bpl INNER JOIN C_Location l ON (bpl.C_Location_ID=l.C_Location_ID)" + " WHERE i.C_BPartner_ID=bpl.C_BPartner_ID AND bpl.AD_Client_ID=i.AD_Client_ID" + " AND DUMP(i.Address1)=DUMP(l.Address1) AND DUMP(i.Address2)=DUMP(l.Address2)" + " AND DUMP(i.City)=DUMP(l.City) AND DUMP(i.Postal)=DUMP(l.Postal) AND DUMP(i.Postal_Add)=DUMP(l.Postal_Add)" + " AND DUMP(i.C_Region_ID)=DUMP(l.C_Region_ID) AND DUMP(i.C_Country_ID)=DUMP(l.C_Country_ID)) " + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL" + " AND I_IsImported='N'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Found Location=" + no); // Interest Area sql = new StringBuilder("UPDATE I_BPartner i " + "SET R_InterestArea_ID=(SELECT R_InterestArea_ID FROM R_InterestArea ia " + "WHERE i.InterestAreaName=ia.Name AND ia.AD_Client_ID=i.AD_Client_ID) " + "WHERE R_InterestArea_ID IS NULL AND InterestAreaName IS NOT NULL" + " AND I_IsImported='N'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Interest Area=" + no); Commit(); // ------------------------------------------------------------------- int noInsert = 0; int noUpdate = 0; IDataReader idr = null; // Go through Records sql = new StringBuilder("SELECT * FROM I_BPartner " + "WHERE I_IsImported='N'").Append(clientCheck); try { //PreparedStatement pstmt = DataBase.prepareStatement(sql.ToString(), Get_TrxName()); //ResultSet rs = pstmt.executeQuery(); idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName()); while (idr.Read()) { X_I_BPartner impBP = new X_I_BPartner(GetCtx(), idr, Get_TrxName()); log.Fine("I_BPartner_ID=" + impBP.GetI_BPartner_ID() + ", C_BPartner_ID=" + impBP.GetC_BPartner_ID() + ", C_BPartner_Location_ID=" + impBP.GetC_BPartner_Location_ID() + ", AD_User_ID=" + impBP.GetAD_User_ID()); // **** Create/Update BPartner **** MBPartner bp = null; if (impBP.GetC_BPartner_ID() == 0) // Insert new BPartner { bp = new MBPartner(impBP); if (bp.Save()) { impBP.SetC_BPartner_ID(bp.GetC_BPartner_ID()); log.Finest("Insert BPartner - " + bp.GetC_BPartner_ID()); noInsert++; } else { sql = new StringBuilder("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||") .Append("Cannot Insert BPartner") .Append("WHERE I_BPartner_ID=").Append(impBP.GetI_BPartner_ID()); DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); continue; } } else // Update existing BPartner { bp = new MBPartner(GetCtx(), impBP.GetC_BPartner_ID(), Get_TrxName()); // if (impBP.getValue() != null) // not to overwite // bp.setValue(impBP.getValue()); if (impBP.GetName() != null) { bp.SetName(impBP.GetName()); bp.SetName2(impBP.GetName2()); } if (impBP.GetDUNS() != null) { bp.SetDUNS(impBP.GetDUNS()); } if (impBP.GetTaxID() != null) { bp.SetTaxID(impBP.GetTaxID()); } if (impBP.GetNAICS() != null) { bp.SetNAICS(impBP.GetNAICS()); } if (impBP.GetC_BP_Group_ID() != 0) { bp.SetC_BP_Group_ID(impBP.GetC_BP_Group_ID()); } if (impBP.GetDescription() != null) { bp.SetDescription(impBP.GetDescription()); } // if (bp.Save()) { log.Finest("Update BPartner - " + bp.GetC_BPartner_ID()); noUpdate++; } else { sql = new StringBuilder("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||") .Append("' Cannot Update BPartner' ") //jz .Append("WHERE I_BPartner_ID=").Append(impBP.GetI_BPartner_ID()); DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); continue; } } // **** Create/Update BPartner Location **** MBPartnerLocation bpl = null; if (impBP.GetC_BPartner_Location_ID() != 0) // Update Location { bpl = new MBPartnerLocation(GetCtx(), impBP.GetC_BPartner_Location_ID(), Get_TrxName()); MLocation location = new MLocation(GetCtx(), bpl.GetC_Location_ID(), Get_TrxName()); location.SetC_Country_ID(impBP.GetC_Country_ID()); location.SetC_Region_ID(impBP.GetC_Region_ID()); location.SetCity(impBP.GetCity()); location.SetAddress1(impBP.GetAddress1()); location.SetAddress2(impBP.GetAddress2()); location.SetPostal(impBP.GetPostal()); location.SetPostal_Add(impBP.GetPostal_Add()); location.SetRegionName(impBP.GetRegionName()); if (!location.Save()) { log.Warning("Location not updated"); } else { bpl.SetC_Location_ID(location.GetC_Location_ID()); } if (impBP.GetPhone() != null) { bpl.SetPhone(impBP.GetPhone()); } if (impBP.GetPhone2() != null) { bpl.SetPhone2(impBP.GetPhone2()); } if (impBP.GetFax() != null) { bpl.SetFax(impBP.GetFax()); } bpl.Save(); } else // New Location if (impBP.GetC_Country_ID() != 0 && impBP.GetAddress1() != null && impBP.GetCity() != null) { MLocation location = new MLocation(GetCtx(), impBP.GetC_Country_ID(), impBP.GetC_Region_ID(), impBP.GetCity(), Get_TrxName()); location.SetAddress1(impBP.GetAddress1()); location.SetAddress2(impBP.GetAddress2()); location.SetPostal(impBP.GetPostal()); location.SetPostal_Add(impBP.GetPostal_Add()); location.SetRegionName(impBP.GetRegionName()); if (location.Save()) { log.Finest("Insert Location - " + location.GetC_Location_ID()); } else { Rollback(); noInsert--; sql = new StringBuilder("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||") .Append("Cannot Insert Location") .Append("WHERE I_BPartner_ID=").Append(impBP.GetI_BPartner_ID()); DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); continue; } // bpl = new MBPartnerLocation(bp); bpl.SetC_Location_ID(location.GetC_Location_ID()); bpl.SetPhone(impBP.GetPhone()); bpl.SetPhone2(impBP.GetPhone2()); bpl.SetFax(impBP.GetFax()); if (bpl.Save()) { log.Finest("Insert BP Location - " + bpl.GetC_BPartner_Location_ID()); impBP.SetC_BPartner_Location_ID(bpl.GetC_BPartner_Location_ID()); } else { Rollback(); noInsert--; sql = new StringBuilder("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||") .Append("Cannot Insert BPLocation") .Append("WHERE I_BPartner_ID=").Append(impBP.GetI_BPartner_ID()); DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); continue; } } // **** Create/Update Contact **** MUser user = null; if (impBP.GetAD_User_ID() != 0) { user = new MUser(GetCtx(), impBP.GetAD_User_ID(), Get_TrxName()); if (user.GetC_BPartner_ID() == 0) { user.SetC_BPartner_ID(bp.GetC_BPartner_ID()); } else if (user.GetC_BPartner_ID() != bp.GetC_BPartner_ID()) { Rollback(); noInsert--; sql = new StringBuilder("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||") .Append("BP of User <> BP") .Append("WHERE I_BPartner_ID=").Append(impBP.GetI_BPartner_ID()); DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); continue; } if (impBP.GetC_Greeting_ID() != 0) { user.SetC_Greeting_ID(impBP.GetC_Greeting_ID()); } String name = impBP.GetContactName(); if (name == null || name.Length == 0) { name = impBP.GetEMail(); } user.SetName(name); if (impBP.GetTitle() != null) { user.SetTitle(impBP.GetTitle()); } if (impBP.GetContactDescription() != null) { user.SetDescription(impBP.GetContactDescription()); } if (impBP.GetComments() != null) { user.SetComments(impBP.GetComments()); } if (impBP.GetPhone() != null) { user.SetPhone(impBP.GetPhone()); } if (impBP.GetPhone2() != null) { user.SetPhone2(impBP.GetPhone2()); } if (impBP.GetFax() != null) { user.SetFax(impBP.GetFax()); } if (impBP.GetEMail() != null) { user.SetEMail(impBP.GetEMail()); } if (impBP.GetBirthday() != null) { user.SetBirthday(impBP.GetBirthday()); } if (bpl != null) { user.SetC_BPartner_Location_ID(bpl.GetC_BPartner_Location_ID()); } if (user.Save()) { log.Finest("Update BP Contact - " + user.GetAD_User_ID()); } else { Rollback(); noInsert--; sql = new StringBuilder("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||") .Append("Cannot Update BP Contact") .Append("WHERE I_BPartner_ID=").Append(impBP.GetI_BPartner_ID()); DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); continue; } } else // New Contact if (impBP.GetContactName() != null || impBP.GetEMail() != null) { user = new MUser(bp); if (impBP.GetC_Greeting_ID() != 0) { user.SetC_Greeting_ID(impBP.GetC_Greeting_ID()); } String name = impBP.GetContactName(); if (name == null || name.Length == 0) { name = impBP.GetEMail(); } user.SetName(name); user.SetTitle(impBP.GetTitle()); user.SetDescription(impBP.GetContactDescription()); user.SetComments(impBP.GetComments()); user.SetPhone(impBP.GetPhone()); user.SetPhone2(impBP.GetPhone2()); user.SetFax(impBP.GetFax()); user.SetEMail(impBP.GetEMail()); user.SetBirthday(impBP.GetBirthday()); if (bpl != null) { user.SetC_BPartner_Location_ID(bpl.GetC_BPartner_Location_ID()); } if (user.Save()) { log.Finest("Insert BP Contact - " + user.GetAD_User_ID()); impBP.SetAD_User_ID(user.GetAD_User_ID()); } else { Rollback(); noInsert--; sql = new StringBuilder("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||") .Append("Cannot Insert BPContact") .Append("WHERE I_BPartner_ID=").Append(impBP.GetI_BPartner_ID()); DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); continue; } } // Interest Area if (impBP.GetR_InterestArea_ID() != 0 && user != null) { MContactInterest ci = MContactInterest.Get(GetCtx(), impBP.GetR_InterestArea_ID(), user.GetAD_User_ID(), true, Get_TrxName()); ci.Save(); // don't subscribe or re-activate } // impBP.SetI_IsImported(X_I_BPartner.I_ISIMPORTED_Yes); impBP.SetProcessed(true); impBP.SetProcessing(false); impBP.Save(); Commit(); } // for all I_Product idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, "", e); Rollback(); } // Set Error to indicator to not imported sql = new StringBuilder("UPDATE I_BPartner " + "SET I_IsImported='N', Updated=SysDate " + "WHERE I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); AddLog(0, null, Utility.Util.GetValueOfDecimal(no), "@Errors@"); AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsert), "@C_BPartner_ID@: @Inserted@"); AddLog(0, null, Utility.Util.GetValueOfDecimal(noUpdate), "@C_BPartner_ID@: @Updated@"); return(""); } // doIt
//public int InsertDimensionAmount(int[] acctSchema, string[] elementType, decimal amount, List<AmountDivisionModel> dimensionLine, Ctx ctx, int DimAmtId) //{ // bool checkTran = true; // int DimAcctTypeId = 0; // int RecordID = -1; // string Sql = ""; // Trx trx = Trx.Get("trxDim" + DateTime.Now.Millisecond); // try // { // dimensionLine = splitAllAccountSchema(acctSchema, dimensionLine); // X_C_DimAmt objDimAmt = new X_C_DimAmt(ctx, DimAmtId, trx); // objDimAmt.SetAmount(amount); // if (!objDimAmt.Save(trx)) // { // checkTran = false; // RecordID = -1; // return RecordID; // } // RecordID = objDimAmt.GetC_DimAmt_ID(); // List<AmountDivisionModel> oldDimensionLine = GetDimensionLine(DimAmtId); // //Check For Value in oldDimensionLine List against DimensionType and Accounting Schema............... // foreach (AmountDivisionModel obj in oldDimensionLine) // { // var abc = dimensionLine.Where(x => x.DimensionTypeVal == obj.DimensionTypeVal && x.AcctSchema == obj.AcctSchema); // if (abc == null) // { // //if Value does not exist in dimension Line than delete from C_DimamtAcctType Table against DimensionType and AccountSchema // //and corresponding Dimension Line Will be deleted.................(cascade). // Sql = "delete from c_dimamtaccttype where c_dimamt_id=" + DimAmtId + " and elementtype='" + obj.DimensionTypeVal + "' and c_acctschema_id=" + obj.AcctSchema; // DB.ExecuteQuery(Sql, null, trx); // oldDimensionLine.RemoveAll(x => x.DimensionTypeVal == obj.DimensionTypeVal && x.AcctSchema == obj.AcctSchema); // } // } // //string sql1 = "delete from c_dimamtline where c_dimamt_id=" + objDimAmt.GetC_DimAmt_ID(); // //DB.ExecuteQuery(sql1, null, trx); // for (int i = 0; i < acctSchema.Length; i++) // { // if (DimAmtId != 0) // { // //if User Change Dimension Type Value from Accounting Schema Element against Accounting Schema............ // //When user update Record against that Value than delete old Value From C_DimAmtAcctType............ // Sql = "select nvl(c_dimamtaccttype_ID,0) from c_dimamtaccttype where c_dimamt_id=" + objDimAmt.GetC_DimAmt_ID() + " and c_acctschema_ID=" + acctSchema[i] + ""; // DimAcctTypeId = Convert.ToInt32(DB.ExecuteScalar(Sql)); // Sql = "select elementType from c_dimamtaccttype where c_dimamtAcctType_id=" + DimAcctTypeId; // string oldElement = Convert.ToString(DB.ExecuteScalar(Sql)); // if (oldElement != elementType[i]) // { // Sql = "delete from c_dimamtaccttype where c_dimamtAcctType_id=" + DimAcctTypeId; // DB.ExecuteQuery(Sql, null, trx); // DimAcctTypeId = 0; // } // } // X_C_DimAmtAcctType objDimAcctType = new X_C_DimAmtAcctType(ctx, DimAcctTypeId, trx); // objDimAcctType.SetC_DimAmt_ID(objDimAmt.GetC_DimAmt_ID()); // objDimAcctType.SetC_AcctSchema_ID(acctSchema[i]); // objDimAcctType.SetElementType(elementType[i]); // if (DimAcctTypeId == 0) // { // if (!objDimAcctType.Save(trx)) // { // checkTran = false; // RecordID = -1; // return RecordID; // } // } // List<AmountDivisionModel> accountDimensionLine = dimensionLine.FindAll(x => x.AcctSchema == acctSchema[i]);//Find Value against Accounting Schema in New List.......... // List<AmountDivisionModel> oldAccountDimensionLine = oldDimensionLine.FindAll(x => x.AcctSchema == acctSchema[i]);//Find Value against Accounting Schema in Old List............... // foreach (AmountDivisionModel mod in accountDimensionLine) // { // List<AmountDivisionModel> oldTempDim = new List<AmountDivisionModel>(); // //Find New List Value in old List............ // //if All Value Matches than Do Nothing.......... // //if Amount Changes in New List than update against that Line ID..... // if (oldAccountDimensionLine.Count > 0) // { // oldTempDim = oldAccountDimensionLine.FindAll(x => x.DimensionNameVal == mod.DimensionNameVal && x.DimensionTypeVal == mod.DimensionTypeVal && x.DiemensionValueAmount == mod.DiemensionValueAmount); // } // if (oldTempDim.Count == 0) // { // int dimAmtLineId = 0; // oldTempDim = oldAccountDimensionLine.FindAll(x => x.DimensionNameVal == mod.DimensionNameVal && x.DimensionTypeVal == mod.DimensionTypeVal); // if (oldTempDim != null) // { // Sql = "select c_dimamtline_id from c_dimamtline cl inner join c_dimamtacctType ct on cl.c_dimamt_id=ct.c_dimamt_id and ct.c_dimamtaccttype_id = cl.c_dimamtaccttype_id " + // " where cl.c_dimamt_id=" + objDimAmt.GetC_DimAmt_ID() + " and cl.c_dimamtacctType_Id=" + DimAcctTypeId; // if (mod.DimensionTypeVal == "AC") // { // Sql += " and cl.c_elementvalue_id=" + mod.DimensionNameVal; // }//Account // else if (mod.DimensionTypeVal == "AY") { Sql += " and cl.c_activity_id=" + mod.DimensionNameVal; }//Activity // else if (mod.DimensionTypeVal == "BP") { Sql += " and cl.c_BPartner_ID=" + mod.DimensionNameVal; }//BPartner // else if (mod.DimensionTypeVal == "LF" || mod.DimensionTypeVal == "LT") { Sql += " and cl.c_location_ID=" + mod.DimensionNameVal; }//Location From//Location To // else if (mod.DimensionTypeVal == "MC") { Sql += " and cl.c_Campaign_ID=" + mod.DimensionNameVal; }//Campaign // else if (mod.DimensionTypeVal == "OO" || mod.DimensionTypeVal == "OT") { Sql += " and cl.Org_ID=" + mod.DimensionNameVal; }//Organization//Org Trx // else if (mod.DimensionTypeVal == "PJ") { Sql += " and cl.c_Project_id=" + mod.DimensionNameVal; }//Project // else if (mod.DimensionTypeVal == "PR") { Sql += " and cl.M_Product_Id=" + mod.DimensionNameVal; }//Product // else if (mod.DimensionTypeVal == "SR") { Sql += " and cl.c_SalesRegion_Id=" + mod.DimensionNameVal; }//Sales Region // else if (mod.DimensionTypeVal == "U1" || mod.DimensionTypeVal == "U2") // { // Sql += " and cl.c_elementvalue_id=" + mod.DimensionNameVal; // }//User List 1//User List 2 // else if (mod.DimensionTypeVal == "X1" || mod.DimensionTypeVal == "X2" || mod.DimensionTypeVal == "X3" || mod.DimensionTypeVal == "X4" || mod.DimensionTypeVal == "X5" || mod.DimensionTypeVal == "X6" || // mod.DimensionTypeVal == "X7" || mod.DimensionTypeVal == "X8" || mod.DimensionTypeVal == "X9") { Sql += " and cl.AD_Column_ID=" + mod.DimensionNameVal; }//User Element 1 to User Element 9 // dimAmtLineId = Convert.ToInt32(DB.ExecuteScalar(Sql)); // } // else // { // dimAmtLineId = 0; // } // X_C_DimAmtLine objDimAmtLine = new X_C_DimAmtLine(ctx, dimAmtLineId, trx); // objDimAmtLine.SetC_DimAmt_ID(objDimAmt.GetC_DimAmt_ID()); // objDimAmtLine.SetC_DimAmtAcctType_ID(objDimAcctType.GetC_DimAmtAcctType_ID()); // objDimAmtLine.SetAmount(mod.DiemensionValueAmount); // if (elementType[i] == "AC") // { // objDimAmtLine.SetC_Element_ID(mod.ElementID); // objDimAmtLine.SetC_ElementValue_ID(mod.DimensionNameVal); // }//Account // else if (elementType[i] == "AY") { objDimAmtLine.SetC_Activity_ID(mod.DimensionNameVal); }//Activity // else if (elementType[i] == "BP") { objDimAmtLine.SetC_BPartner_ID(mod.DimensionNameVal); }//BPartner // else if (elementType[i] == "LF" || elementType[i] == "LT") { objDimAmtLine.SetC_Location_ID(mod.DimensionNameVal); }//Location From//Location To // else if (elementType[i] == "MC") { objDimAmtLine.SetC_Campaign_ID(mod.DimensionNameVal); }//Campaign // else if (elementType[i] == "OO" || elementType[i] == "OT") { objDimAmtLine.SetOrg_ID(mod.DimensionNameVal); }//Organization//Org Trx // else if (elementType[i] == "PJ") { objDimAmtLine.SetC_Project_ID(mod.DimensionNameVal); }//Project // else if (elementType[i] == "PR") { objDimAmtLine.SetM_Product_ID(mod.DimensionNameVal); }//Product // else if (elementType[i] == "SA") { }//Sub Account // else if (elementType[i] == "SR") { objDimAmtLine.SetC_SalesRegion_ID(mod.DimensionNameVal); }//Sales Region // else if (elementType[i] == "U1" || elementType[i] == "U2") // { // objDimAmtLine.SetC_Element_ID(mod.ElementID); // objDimAmtLine.SetC_ElementValue_ID(mod.DimensionNameVal); // }//User List 1//User List 2 // else if (elementType[i] == "X1" || elementType[i] == "X2" || elementType[i] == "X3" || elementType[i] == "X4" || elementType[i] == "X5" || elementType[i] == "X6" || // elementType[i] == "X7" || elementType[i] == "X8" || elementType[i] == "X9") { objDimAmtLine.SetAD_Column_ID(mod.DimensionNameVal); }//User Element 1 to User Element 9 // if (!objDimAmtLine.Save(trx)) // { // checkTran = false; // RecordID = -1; // //return RecordID; // break; // } // //Remove Value From old List After that value is passed or Updated............ // oldAccountDimensionLine.Remove(oldAccountDimensionLine.Find(a => a.AcctSchema == mod.AcctSchema && a.DimensionTypeVal == mod.DimensionTypeVal && a.DimensionNameVal == mod.DimensionNameVal)); // } // else // { // oldAccountDimensionLine.Remove(oldAccountDimensionLine.Find(a => a.AcctSchema == mod.AcctSchema && a.DimensionTypeVal == mod.DimensionTypeVal && a.DimensionNameVal == mod.DimensionNameVal)); // } // } // foreach (AmountDivisionModel oldMod in oldAccountDimensionLine) // { // //Delete Remaing Value Which are in old List and from C_dimAmtLine Table.................... // Sql = "delete from c_dimamtline where c_dimamtline_id=" + oldMod.AmtLineID; // DB.ExecuteQuery(Sql, null, trx); // } // } // } // catch (Exception e) // { // checkTran = false; // RecordID = -1; // } // finally // { // if (!checkTran) // { // trx.Rollback(); // log.Warning("Some error occured while saving Dimension"); // } // else // { // trx.Commit(); // } // } // return RecordID; //} public List <AmountDivisionModel> GetDimensionLine(Ctx ctx, int[] accountingSchema, int dimensionID, int DimensionLineID = 0, int pageNo = 0, int pSize = 0) { int tempRecid = pSize * (pageNo - 1); List <AmountDivisionModel> objAmtdimModel = new List <AmountDivisionModel>(); foreach (int acctId in accountingSchema) { string uElementTable = ""; string uElementColumn = ""; if (objAmtdimModel.Count != 0) { break; } int tempDimensionID = Convert.ToInt32(DB.ExecuteScalar("select nvl(c_dimamt_id,0) as DimID from c_dimamtline where c_dimamt_id=" + dimensionID + "")); if (tempDimensionID != 0) { string uQuery = "select main.TableName,listagg(TO_CHAR(('ac.'||main.Colname)),'||''_''||') WITHIN GROUP(order by main.ColId) ColName from " + "(select distinct tab.tableName as TableName,col2.columnName as Colname,col2.ad_column_id as ColId from c_dimamtaccttype ct " + "inner join c_dimamtline cl on cl.c_dimamt_id=ct.c_dimamt_id and cl.c_dimamtaccttype_id=ct.c_dimamtaccttype_id " + "inner join c_acctschema_element se on se.c_acctschema_id=ct.c_acctschema_id and se.elementtype=ct.elementtype " + "inner join ad_column col1 on col1.ad_column_id=se.ad_column_id " + "inner join ad_table tab on tab.ad_table_id=col1.ad_table_id " + "inner join ad_column col2 on col2.ad_table_id=tab.ad_table_id and col2.isidentifier='Y' " + "where ct.c_dimamt_id=" + dimensionID + " and ct.c_acctschema_id=" + acctId + " order by col2.ad_column_id) main " + "group by main.TableName"; DataSet dsUelement = DB.ExecuteDataset(uQuery); if (dsUelement != null && dsUelement.Tables[0].Rows.Count > 0) { uElementTable = Convert.ToString(dsUelement.Tables[0].Rows[0][0]); uElementColumn = Convert.ToString(dsUelement.Tables[0].Rows[0][1]); } string sql = "SELECT distinct COALESCE(cl.ad_column_id,cl.C_ELEMENTVALUE_ID,cl.c_activity_id,cl.C_BPARTNER_ID,cl.C_CAMPAIGN_ID,cl.C_LOCATION_ID,cl.C_PROJECT_ID ,cl.C_SALESREGION_ID,cl.M_PRODUCT_ID,cl.ORG_ID) AS DimensionValue," + " cl.amount,ct.c_acctschema_id,ct.elementtype,rl.Name as DimensionType, " + " COALESCE(o.name "; if (uElementColumn != "") { sql += " ,(" + uElementColumn + ") "; } sql += " ,cel.Name,act.Name,cb.Name,cc.Name,cloc.address1,cpr.Name,cs.Name,mp.NAME) AS DimensionName ,nvl(cl.C_ELEMENT_ID,0) as ElementID,cl.c_dimamtline_id as LineID, cl.C_BPartner_ID, cb.Name AS BPartnerName " + " from c_dimamt cdm "; //{ // sql += " left join c_dimamtaccttype ct on cdm.c_dimamt_id=ct.c_dimamt_id " + // " Left join c_dimamtline cl ON cl.c_dimAmt_ID=ct.c_dimAmt_ID and cl.c_dimamtaccttype_id=ct.c_dimamtaccttype_id" + // " left JOIN c_acctschema_element rl ON ct.elementtype =rl.elementtype AND ct.c_acctschema_id=rl.c_Acctschema_ID " + // " left join ad_ref_list adref on adref.value=ct.elementtype " + // " left join ad_column adc on adc.AD_Reference_Value_ID=adref.AD_Reference_ID and adc.export_id='VIS_2663'"; //} //else //{ sql += " inner join c_dimamtaccttype ct on cdm.c_dimamt_id=ct.c_dimamt_id " + " inner join c_dimamtline cl ON cl.c_dimAmt_ID=ct.c_dimAmt_ID and cl.c_dimamtaccttype_id=ct.c_dimamtaccttype_id" + " inner JOIN c_acctschema_element rl ON ct.elementtype =rl.elementtype AND ct.c_acctschema_id=rl.c_Acctschema_ID " + " inner join ad_ref_list adref on adref.value=ct.elementtype " + " inner join ad_column adc on adc.AD_Reference_Value_ID=adref.AD_Reference_ID and adc.export_id='VIS_2663'"; // } if (uElementTable != "") { sql += " LEFT JOIN " + uElementTable + " ac ON cl.ad_column_ID=ac." + uElementTable + "_ID "; } sql += " LEFT JOIN c_activity act ON cl.c_activity_id=act.c_activity_id " + " LEFT JOIN C_BPARTNER cb ON cl.C_BPARTNER_ID=cb.C_BPARTNER_ID " + " LEFT JOIN C_CAMPAIGN cc ON cl.C_CAMPAIGN_ID=cc.C_CAMPAIGN_ID " + " LEFT JOIN C_ELEMENTVALUE cel ON cl.C_ELEMENTVALUE_ID=cel.C_ELEMENTVALUE_ID " + " LEFT JOIN C_ELEMENT el ON cl.C_ELEMENT_ID=el.C_ELEMENT_ID " + " LEFT JOIN C_LOCATION cloc ON cl.C_LOCATION_ID=cloc.C_LOCATION_ID " + " LEFT JOIN C_PROJECT cpr ON cl.C_PROJECT_ID=cpr.C_PROJECT_ID " + " LEFT JOIN C_SALESREGION cs ON cl.C_SALESREGION_ID=cs.C_SALESREGION_ID " + " LEFT JOIN M_PRODUCT mp ON cl.M_PRODUCT_ID=mp.M_PRODUCT_ID " + " LEFT JOIN AD_org o ON cl.org_id=o.AD_org_id " + " WHERE cdm.c_dimamt_ID=" + dimensionID + " and ct.c_acctschema_id=" + acctId; if (DimensionLineID != 0) { sql += " and cl.c_dimamtline_id=" + DimensionLineID; } sql += " order by ct.c_acctschema_id"; DataSet ds = new DataSet(); if (pSize == 0 || DimensionLineID != 0) { ds = DB.ExecuteDataset(sql); } else { ds = VIS.DBase.DB.ExecuteDatasetPaging(sql, pageNo, pSize); } string sqlcount = "select count(*),sum(lineTotalAmount) as lineTotalAmount from ( " + " SELECT distinct (cl.Amount) AS LineTotalAmount,cdm.c_dimamt_id,ct.elementtype,cl.c_dimamtline_id from c_dimamt cdm "; if (tempDimensionID == 0) { sqlcount += " left join c_dimamtaccttype ct on cdm.c_dimamt_id=ct.c_dimamt_id " + " Left join c_dimamtline cl ON cl.c_dimAmt_ID=ct.c_dimAmt_ID and cl.c_dimamtaccttype_id=ct.c_dimamtaccttype_id" + " left JOIN c_acctschema_element rl ON ct.elementtype =rl.elementtype AND ct.c_acctschema_id=rl.c_Acctschema_ID " + " left join ad_ref_list adref on adref.value=ct.elementtype " + " left join ad_column adc on adc.AD_Reference_Value_ID=adref.AD_Reference_ID and adc.export_id='VIS_2663'"; } else { sqlcount += " inner join c_dimamtaccttype ct on cdm.c_dimamt_id=ct.c_dimamt_id " + " inner join c_dimamtline cl ON cl.c_dimAmt_ID=ct.c_dimAmt_ID and cl.c_dimamtaccttype_id=ct.c_dimamtaccttype_id" + " inner JOIN c_acctschema_element rl ON ct.elementtype =rl.elementtype AND ct.c_acctschema_id=rl.c_Acctschema_ID " + " inner join ad_ref_list adref on adref.value=ct.elementtype " + " inner join ad_column adc on adc.AD_Reference_Value_ID=adref.AD_Reference_ID and adc.export_id='VIS_2663'"; } sqlcount += " LEFT JOIN ad_column ac ON cl.ad_column_ID=ac.ad_column_ID " + " LEFT JOIN c_activity act ON cl.c_activity_id=act.c_activity_id " + " LEFT JOIN C_BPARTNER cb ON cl.C_BPARTNER_ID=cb.C_BPARTNER_ID " + " LEFT JOIN C_CAMPAIGN cc ON cl.C_CAMPAIGN_ID=cc.C_CAMPAIGN_ID " + " LEFT JOIN C_ELEMENTVALUE cel ON cl.C_ELEMENTVALUE_ID=cel.C_ELEMENTVALUE_ID " + " LEFT JOIN C_ELEMENT el ON cl.C_ELEMENT_ID=el.C_ELEMENT_ID " + " LEFT JOIN C_LOCATION cloc ON cl.C_LOCATION_ID=cloc.C_LOCATION_ID " + " LEFT JOIN C_PROJECT cpr ON cl.C_PROJECT_ID=cpr.C_PROJECT_ID " + " LEFT JOIN C_SALESREGION cs ON cl.C_SALESREGION_ID=cs.C_SALESREGION_ID " + " LEFT JOIN M_PRODUCT mp ON cl.M_PRODUCT_ID=mp.M_PRODUCT_ID " + " LEFT JOIN AD_org o ON cl.org_id=o.AD_org_id " + " WHERE cdm.c_dimamt_ID=" + dimensionID + " and ct.c_acctschema_id=" + acctId + " order by ct.c_acctschema_id ) main"; DataSet Record = DB.ExecuteDataset(sqlcount); if (ds != null && ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { AmountDivisionModel obj = new AmountDivisionModel(); obj.recid = tempRecid + (i + 1); obj.AcctSchema = Convert.ToInt32(ds.Tables[0].Rows[i]["c_acctschema_id"]); if (ds.Tables[0].Rows[i]["amount"] != DBNull.Value) { obj.DimensionValueAmount = DisplayType.GetNumberFormat(DisplayType.Amount).GetFormatedValue(Convert.ToDecimal(ds.Tables[0].Rows[i]["amount"])); obj.CalculateDimValAmt = Convert.ToDecimal(ds.Tables[0].Rows[i]["amount"]); } else { obj.DimensionValueAmount = DisplayType.GetNumberFormat(DisplayType.Amount).GetFormatedValue(0); obj.CalculateDimValAmt = 0; } obj.DimensionName = Convert.ToString(ds.Tables[0].Rows[i]["DimensionName"]); if (ds.Tables[0].Rows[i]["DimensionValue"] != DBNull.Value) { obj.DimensionNameVal = Convert.ToInt32(ds.Tables[0].Rows[i]["DimensionValue"]); } else { obj.DimensionNameVal = 0; } obj.C_BPartner_ID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]); obj.C_BPartner = Convert.ToString(ds.Tables[0].Rows[i]["BPartnerName"]); obj.DimensionType = Convert.ToString(ds.Tables[0].Rows[i]["DimensionType"]); obj.DimensionTypeVal = Convert.ToString(ds.Tables[0].Rows[i]["elementtype"]); if (obj.DimensionTypeVal == "LF" || obj.DimensionTypeVal == "LT") { MLocation objMLocation = new MLocation(ctx, obj.DimensionNameVal, null); obj.DimensionName = objMLocation.ToString(); } obj.ElementID = Convert.ToInt32(ds.Tables[0].Rows[i]["ElementID"]); if (ds.Tables[0].Rows[i]["LineID"] != DBNull.Value) { obj.lineAmountID = Convert.ToString(ds.Tables[0].Rows[i]["LineID"]); } else { obj.lineAmountID = "0"; } if (Record != null && Record.Tables[0].Rows.Count > 0) { if (Record.Tables[0].Rows[0][0] != DBNull.Value) { obj.TotalRecord = Convert.ToInt32(Record.Tables[0].Rows[0][0]); } else { obj.TotalRecord = 0; } if (Record.Tables[0].Rows[0][1] != DBNull.Value) { obj.TotalLineAmount = Convert.ToDecimal(Record.Tables[0].Rows[0][1]); } else { obj.TotalLineAmount = 0; } } else { obj.TotalRecord = 0; obj.TotalLineAmount = 0; } objAmtdimModel.Add(obj); } } } } return(objAmtdimModel); }
} // prepare /// <summary> /// DoIt /// </summary> /// <returns> Message</returns> protected override String DoIt() { int AD_Registration_ID = GetRecord_ID(); log.Info("doIt - AD_Registration_ID=" + AD_Registration_ID); // Check Ststem MSystem sys = MSystem.Get(GetCtx()); if (sys.GetName().Equals("?") || sys.GetName().Length < 2) { throw new Exception("Set System Name in System Record"); } if (sys.GetUserName().Equals("?") || sys.GetUserName().Length < 2) { throw new Exception("Set User Name (as in Web Store) in System Record"); } if (sys.GetPassword().Equals("?") || sys.GetPassword().Length < 2) { throw new Exception("Set Password (as in Web Store) in System Record"); } // Registration M_Registration reg = new M_Registration(GetCtx(), AD_Registration_ID, Get_TrxName()); // Location MLocation loc = null; if (reg.GetC_Location_ID() > 0) { loc = new MLocation(GetCtx(), reg.GetC_Location_ID(), Get_TrxName()); if (loc.GetCity() == null || loc.GetCity().Length < 2) { throw new Exception("No City in Address"); } } if (loc == null) { throw new Exception("Please enter Address with City"); } // Create Query String //String enc = WebEnv.ENCODING; // Send GET Request StringBuilder urlString = new StringBuilder("http://www.ViennaAdvantage.com") .Append("/wstore/registrationServlet?"); // System Info urlString.Append("Name=").Append(HttpUtility.UrlEncode(sys.GetName(), UTF8Encoding.UTF8)) .Append("&UserName="******"&Password="******"&Description=").Append(HttpUtility.UrlEncode(reg.GetDescription(), UTF8Encoding.UTF8)); } urlString.Append("&IsInProduction=").Append(reg.IsInProduction() ? "Y" : "N"); if (reg.GetStartProductionDate() != null) { urlString.Append("&StartProductionDate=").Append(HttpUtility.UrlEncode(Convert.ToString(reg.GetStartProductionDate()), UTF8Encoding.UTF8)); } urlString.Append("&IsAllowPublish=").Append(reg.IsAllowPublish() ? "Y" : "N") .Append("&NumberEmployees=").Append(HttpUtility.UrlEncode(Convert.ToString(reg.GetNumberEmployees()), UTF8Encoding.UTF8)) .Append("&C_Currency_ID=").Append(HttpUtility.UrlEncode(Convert.ToString(reg.GetC_Currency_ID()), UTF8Encoding.UTF8)) .Append("&SalesVolume=").Append(HttpUtility.UrlEncode(Convert.ToString(reg.GetSalesVolume()), UTF8Encoding.UTF8)); if (reg.GetIndustryInfo() != null && reg.GetIndustryInfo().Length > 0) { urlString.Append("&IndustryInfo=").Append(HttpUtility.UrlEncode(reg.GetIndustryInfo(), UTF8Encoding.UTF8)); } if (reg.GetPlatformInfo() != null && reg.GetPlatformInfo().Length > 0) { urlString.Append("&PlatformInfo=").Append(HttpUtility.UrlEncode(reg.GetPlatformInfo(), UTF8Encoding.UTF8)); } urlString.Append("&IsRegistered=").Append(reg.IsRegistered() ? "Y" : "N") .Append("&Record_ID=").Append(HttpUtility.UrlEncode(Convert.ToString(reg.GetRecord_ID()), UTF8Encoding.UTF8)); // Address urlString.Append("&City=").Append(HttpUtility.UrlEncode(loc.GetCity(), UTF8Encoding.UTF8)) .Append("&C_Country_ID=").Append(HttpUtility.UrlEncode(Convert.ToString(loc.GetC_Country_ID()), UTF8Encoding.UTF8)); // Statistics if (reg.IsAllowStatistics()) { urlString.Append("&NumClient=").Append(HttpUtility.UrlEncode(Convert.ToString( DataBase.DB.GetSQLValue(null, "SELECT Count(*) FROM AD_Client")), UTF8Encoding.UTF8)) .Append("&NumOrg=").Append(HttpUtility.UrlEncode(Convert.ToString( DataBase.DB.GetSQLValue(null, "SELECT Count(*) FROM AD_Org")), UTF8Encoding.UTF8)) .Append("&NumBPartner=").Append(HttpUtility.UrlEncode(Convert.ToString( DataBase.DB.GetSQLValue(null, "SELECT Count(*) FROM C_BPartner")), UTF8Encoding.UTF8)) .Append("&NumUser="******"SELECT Count(*) FROM AD_User")), UTF8Encoding.UTF8)) .Append("&NumProduct=").Append(HttpUtility.UrlEncode(Convert.ToString( DataBase.DB.GetSQLValue(null, "SELECT Count(*) FROM M_Product")), UTF8Encoding.UTF8)) .Append("&NumInvoice=").Append(HttpUtility.UrlEncode(Convert.ToString( DataBase.DB.GetSQLValue(null, "SELECT Count(*) FROM C_Invoice")), UTF8Encoding.UTF8)); } log.Fine(urlString.ToString()); // Send it //URL url = new URL (urlString.toString()); // Url url=new Url(urlString.ToString()); Uri url = new Uri(urlString.ToString()); StringBuilder sb = new StringBuilder(); try { //URLConnection uc = url.openConnection(); //System.IO.StreamReader inn = new System.IO.StreamReader(urlString.ToString()); //InputStreamReader in = new InputStreamReader(uc.getInputStream()); WebRequest request = WebRequest.Create(url.ToString()); WebResponse response = (WebResponse)request.GetResponse(); Stream stream = response.GetResponseStream(); byte[] buffer = new byte[stream.Length]; int c; int len = Convert.ToInt32(stream.Length); String tempstring = null; while ((c = stream.Read(buffer, 0, len)) > 0) { //sb.Append((char)c); tempstring = Encoding.ASCII.GetString(buffer, 0, len); sb.Append(tempstring); } } catch (Exception e) { log.Log(Level.SEVERE, "Connect - " + e.ToString()); throw new Exception("Cannot connect to Server - Please try later"); } // String info = sb.ToString(); log.Info("Response=" + info); // Record at the end int index = sb.ToString().IndexOf("Record_ID="); if (index != -1) { try { int Record_ID = Utility.Util.GetValueOfInt(sb.ToString().Substring(index + 10)); reg.SetRecord_ID(Record_ID); reg.SetIsRegistered(true); reg.Save(); // info = info.Substring(0, index); } catch (Exception e) { log.Log(Level.SEVERE, "Record - ", e); } } return(info); } // doIt