C# method direct

Tripb44 · January 18, 2021, 9:42pm

I am needing to grab the sold to zip code. There is a existing method directive using the ship to zip. C# below. Code below for reference.

//declaring tables
Erp.Tables.Customer Customer;
Erp.Tables.CustIC CustIC;
Erp.Tables.ShipTo ShipTo;
Ice.Tables.UD05 UD05StateOnly;
Ice.Tables.UD05 UD05Zip;

//declaring variables
string ShipToState = "";
string ShipToZip = "";
GDSRep = "";
bool update_OR = false;

using(System.Transactions.TransactionScope scope = IceDataContext.CreateDefaultTransactionScope())
{
  foreach (var qd_xrow in (from qd_xrow in ttQuoteDtl 
  where qd_xrow.Company == Session.CompanyID && qd_xrow.RowMod == "A"
  select qd_xrow))
  {
    foreach (var q_xrow in (from q_xrow in Db.QuoteHed 
    where q_xrow.Company == Session.CompanyID && q_xrow.QuoteNum == qd_xrow.QuoteNum
    select q_xrow))
    {   
      /* look up customer */
      Customer = (from c_row in Db.Customer  
      where c_row.Company == q_xrow.Company && c_row.CustNum == q_xrow.CustNum 
      select c_row).FirstOrDefault();
      
      if (Customer != null)    /* customer record available */
      {
        /*look up vertical*/
        CustIC = (from vert_row in Db.CustIC  
        where vert_row.Company == Customer.Company && vert_row.CustNum == Customer.CustNum && vert_row.ShipToNum == "" && vert_row.Primary == true
        select vert_row).FirstOrDefault();
        if (CustIC != null)    /* custIC of type record available */
        {
          vertical = CustIC.ICCode;
        }
      }
    
  if(q_xrow.Character10 == "" || containsGDS == true) /*if the rep group is currently blank or is populated but the first GD line is getting added*/
  {
  
    if(qd_xrow.ProdCode == "2010") /* gd lines only*/
    {
      /*This code finds the Ship-To. Since this code is being placed at the quote to order transition we do not need to look at the OrderRel level as the whole Quote goes to the same place*/
      if (q_xrow.UseOTS)       
      {
      /*  if one time ship to do this code */
      ShipToState = Convert.ToString(q_xrow.OTSState);
      ShipToZip = Convert.ToString(q_xrow.OTSZIP);
      }
      else    
      {
      /*if not a OTS use the ship to address*/
      /* look up Ship To */
      ShipTo = (from st_row in Db.ShipTo  
      where st_row.Company == q_xrow.Company && st_row.CustNum == q_xrow.ShipToCustNum && st_row.ShipToNum == q_xrow.ShipToNum
      select st_row).FirstOrDefault();
      if (ShipTo != null)    /* ShipTo record available */
      {
        ShipToState = Convert.ToString(ShipTo.State);
        ShipToZip = Convert.ToString(ShipTo.ZIP);
      }
      }
    
      string shiptozipcode = ShipToZip.SubString(0,5); /*declaring final ship to zip code variable*/
    
      /*ud05zip is variable to hold the UD05 record where the state and zip code are maintained for the corresponding rep group*/
      UD05Zip = (from ud05_row in Db.UD05
      where ud05_row.Company == Session.CompanyID && ud05_row.Key2 == ShipToState && ud05_row.Key3 == shiptozipcode
      select ud05_row).FirstOrDefault();
      /*ud05stateonly is variable to hold the UD05 record where the state is maintained and the zip code is blank*/
    
      UD05StateOnly = (from ud05_row in Db.UD05
      where ud05_row.Company == Session.CompanyID && ud05_row.Key2 == ShipToState && ud05_row.Key3 == ""
      select ud05_row).FirstOrDefault();   
    
      if(UD05Zip != null)
      {
      /*if UD05 has a record for the state and zip*/
      GDSRep = Convert.ToString(UD05Zip.Character01);
      }
      else if (UD05StateOnly != null)
      {
      /*if UD05 only has a record of the state with blank zip*/
      GDSRep = Convert.ToString(UD05StateOnly.Character01);
      }          
      else
      {
      /*if that state and or zip combination is not found default to test*/
      GDSRep = "Test";
      } 
    
      q_xrow.Character10 = GDSRep;
      update_OR = true;
    }
    
    if(vertical == "CRITFAC" && qd_xrow.ProdCode != "2010") /*if CF customer but non gds order default to test*/
    {
      q_xrow.Character10 = "test";
      update_OR = true;
    }
      
    } 
  }  
  }
  
  if(update_OR == true)
  {
    Db.Validate();  /* udpate OrderRel */
    scope.Complete();
  }
}

Jason_Woods · January 19, 2021, 6:33am

FYI, this code can definitely be optimized.

With some assumptions as to what you are doing, here is working code:

foreach (var tt in ttQuoteDtl.Where(tt => !tt.Unchanged()))
{
  string soldToZip = (from Q in Db.QuoteHed.Where(Q => Q.Company == tt.Company && Q.QuoteNum == tt.QuoteNum)
                        join C in Db.Customer on
                          new {Q.Company, CustNum = Q.BTCustNum} equals
                          new {C.Company, C.CustNum}
                        select C.Zip).FirstOrDefault();
}

Tripb44 · January 19, 2021, 2:40pm

So current state has the Ship to rep group(Character10 in UD table) being populated based on the ship to zip. I am needing to add the sold to rep group(Character11 in UD table) which needs to be populated based on the sold to zip. I am new to c# so am trying to link all of these up.

Jason_Woods · January 19, 2021, 5:23pm

Since there is custom logic behind the scenes on this one, we would need more details I believe. I did run through optimizing this code (if I understood it correctly) to be able to see what it was really doing. I have no guarantee this will work:

//declaring variables
string ShipToState = "";
string ShipToZip = "";
GDSRep = "";
bool update_OR = false;

using(System.Transactions.TransactionScope scope = IceDataContext.CreateDefaultTransactionScope())
{
	foreach (var tt in ttQuoteDtl.Where(tt => tt.Added()))
	{
		foreach (var QH in Db.QuoteHed.Where(QH => QH.Company == Stt.Company && QH.QuoteNum == tt.QuoteNum))
		{
			if (tt.ProdCode == "2010")
			{
				if(QH.Character10 == "" ) //if the rep group is currently blank or is populated but the first GD line is getting added/
				{
					/*This code finds the Ship-To. Since this code is being placed at the quote to order transition we do not need to look at the OrderRel level as the whole Quote goes to the same place*/
					if (QH.UseOTS)       
					{
						/*  if one time ship to do this code */
						ShipToState = QH.OTSState;
						ShipToZip = QH.OTSZIP;
					}
					else    
					{
						/*if not a OTS use the ship to address*/
						/* look up Ship To */
						ShipTo = (from st_row in Db.ShipTo  
						where st_row.Company == QH.Company && st_row.CustNum == QH.ShipToCustNum && st_row.ShipToNum == QH.ShipToNum
						select new{st_row.State, st_row.ZIP}).FirstOrDefault();
						if (ShipTo != null)    /* ShipTo record available */
						{
							ShipToState = ShipTo.State;
							ShipToZip = ShipTo.ZIP;
						}
					}

					string shiptozipcode = ShipToZip.SubString(0,5); /*declaring final ship to zip code variable*/

					GDSRep = (from ud05_row in Db.UD05
						where ud05_row.Company == Session.CompanyID && ud05_row.Key2 == ShipToState && ud05_row.Key3 == shiptozipcode
						select ud05_row.Character01).FirstOrDefault();
					if (GDSRep == "")
					{
						/*ud05stateonly is variable to hold the UD05 record where the state is maintained and the zip code is blank*/
						GDSRep = (from ud05_row in Db.UD05
							where ud05_row.Company == Session.CompanyID && ud05_row.Key2 == ShipToState && ud05_row.Key3 == ""
							select ud05_row.Character01).FirstOrDefault();
					}
					if (GDSRep == "")
					{
						GDSRep = "Test";
					}

					QH.Character10 = GDSRep;
					update_OR = true;
				}
			}
			else
			{
				vertical = Db.CustIC.Where(C => C.Company == QH.Company && C.CustNum == QH.CustNum && C.ShipToNum == "" && C.Primary).Select(C => C.ICCode).FirstOrDefault();
				if(vertical == "CRITFAC" ) /*if CF customer but non gds order default to test*/
				{
				  QH.Character10 = "test";
				  update_OR = true;
				}
			}
		}
	}

	if(update_OR == true)
	{
		Db.Validate(); /* udpate OrderRel */
		scope.Complete();
	}
}

Tripb44 · January 19, 2021, 6:05pm

So with this code you wrote. What would be the code to set Character10 in the QuoteHed_UD table to look up the soldtoZip from Ice.UD05.Key3 and grab Ice.UD05.Character01 and set Character10 to Ice.UD05.Character01 which would be the sold to rep group. UD05 is the table that hold the zip codes with the rep group for reference.

Jason_Woods · January 19, 2021, 6:07pm

It’s in there:

GDSRep = (from ud05_row in Db.UD05
						where ud05_row.Company == Session.CompanyID && ud05_row.Key2 == ShipToState && ud05_row.Key3 == shiptozipcode
						select ud05_row.Character01).FirstOrDefault();
					if (GDSRep == "")
					{
						/*ud05stateonly is variable to hold the UD05 record where the state is maintained and the zip code is blank*/
						GDSRep = (from ud05_row in Db.UD05
							where ud05_row.Company == Session.CompanyID && ud05_row.Key2 == ShipToState && ud05_row.Key3 == ""
							select ud05_row.Character01).FirstOrDefault();
					}
					if (GDSRep == "")
					{
						GDSRep = "Test";
					}

					QH.Character10 = GDSRep;

Tripb44 · January 19, 2021, 6:17pm

Isn’t that looking at the ship to zip to return the associated Character01 from ud05 based on ud05.Key3? I need that but to look up the Sold to zip and not the ship to as they may differ.

Jason_Woods · January 19, 2021, 6:27pm

You asked about QuoteHed.Character10 in your last post. This does not solve your original request (to populated Character11), only improves the old code (hopefully).
If it is going into the same code, I would likely re-write more for efficiency.

Tripb44 · January 19, 2021, 6:31pm

End goal is to populate Quotehed.Character11 with the same code as the original but instead of looking at the ship to zip code to get the rep group from UD05 it would look at the sold to zip code to get the rep group from UD05. I hope that is more clear. I am new to C# so deploying this with custom code is new.

Jason_Woods · January 20, 2021, 1:04am

Hopefully the new code is easier to read as you go through the journey of learning C#. There are a lot of tools out there to help and this forum can definitely point you in the right directions.