public void InsertIntoResponseTable(string source, string cid, string transfertypeId, Dictionary <string, List <string> > dicPairs, Hashtable ht, string comment, string fname, string mname, string lname, string businessname, string formatname, string bgfirstname, string bgmiddlename, string bglastname, string bgbusinessname, string address1, string address2, string city, string state, string zip, string nametype, string entitytypecode, string year, string stateid, string locationstateabbrev, string countycode, string locationcountyname, string commoditycode, string typecode, string practicecode, string landtypecode, string farmnumber, string tractnumber, string fieldid, string fieldnumber, string cropcode, string croptypecode, string cropintendedusecode, string irricode, string plantprimcode, string plantseccode, string plantmulticode, string croplandindicator, string cropfieldrep, string cropdeterim, string lastdatechanged, string customernumber, string businesspartycode, string landid, string rluid, string cluid, string cropsharepercentage, string settlementid)
{
string query;
string taxQuery = "";
int transferDataId;
SqlCommand command;
Dictionary <int, List <string> > taxDetails = new Dictionary <int, List <string> >();
if (transfertypeId.Equals("2") || transfertypeId.Equals("4"))
{
taxQuery = "select pii.taxpayerid, pii.taxpayertypeid from tblclaimant c join tblpiidata pii on pii.foreignkeyid = c.claimantid and pii.foreignkeyrefid = 1 where c.settlementid = @claimantid";
}
else if (transfertypeId.Equals("13") || transfertypeId.Equals("15"))
{
taxQuery = "select sws.taxpayerid, sws.taxpayertypeid from tblclaimant c join tblsws02form sws on sws.claimantid = c.claimantid and sws.swstypeid = 2 where c.settlementid = @claimantid and InvalidTaxID = 0";
}
else if (transfertypeId.Equals("17") || transfertypeId.Equals("19"))
{
taxQuery = "select swsd.taxpayerid, swsd.taxpayertypeid from tblclaimant c join tblsws02formdetails swsd on swsd.claimantid = c.claimantid and swsd.swstypeid = 2 where c.settlementid = @claimantid and InvalidTaxID = 0";
}
else if (transfertypeId.Equals("23") || transfertypeId.Equals("25"))
{
taxQuery = "select swsd.taxpayerid, swsd.taxpayertypeid from tblclaimant c join tblsws02formdetails swsd on swsd.claimantid = c.claimantid and swsd.swstypeid in (4,5) where c.settlementid = @claimantid and InvalidTaxID = 0";
}
SqlParameter parametercid = new SqlParameter("@claimantid", SqlDbType.NVarChar);
parametercid.Value = settlementid;
taxDetails = db.GetRecords(connectionString, taxQuery, CommandType.Text, parametercid);
List <string> taxInfo = taxDetails[1];
List <string> cidTType;
switch (source)
{
case "FSA":
if (dicPairs.ContainsKey(cid))
{
cidTType = dicPairs[cid];
if (cidTType.Contains(transfertypeId))
{
transferDataId = (int)ht[transfertypeId];
query = "Insert Into dbo.tblFSAResponseDetail (TransferDataID, TaxID, taxtype, comment, FirstName, LastName, FormattedName," +
" Address1, Address2, City, State, ZipCode, NameType," +
"ProgramYear, StateID, CountyCode, FarmNumber, TractNumber, FieldID, FieldNumber," +
" CropCode, CropTypeCode, CropIntendedUseCode, IrrigationPracticeCode," +
" PlantingPrimStatusCode, PlantingSecStatusCode, PlantingMultiCropCode, " +
"CroplandIndicator, CropFieldRepQuantity, CropFieldDetermQuantity, LastDateChanged, CustomerNumber, BusinessPartyTypeCode, CropSharePercentage, SettlementID, ProcessedDate)" +
"VALUES(@TransferDataID, @taxId, @taxType, @comment, @fname, @lname, @formatname, " +
"@address1, @address2, @city, @state, @zip, @nametype, " +
"@programyear, @stateid, @countycode, @farmnumber, @tractnumber, @fieldid, @fieldnumber," +
"@cropcode, @croptypecode, @cropintendedusecode, @irricode, @plantprimcode, @plantsecode, @plantmulticode," +
"@croplandindicator, @croprep, @cropdeterim, @lastdate, @customernumber, @businesspartycode, @sharepercentage, @settlementid, @processeddate)";
command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@TransferDataID", transferDataId);
command.Parameters.AddWithValue("@taxId", taxInfo[0]);
command.Parameters.AddWithValue("@taxType", taxInfo[1]);
command.Parameters.AddWithValue("@comment", comment);
command.Parameters.AddWithValue("@fname", fname);
command.Parameters.AddWithValue("@lname", lname);
command.Parameters.AddWithValue("@formatname", formatname);
command.Parameters.AddWithValue("@address1", address1);
command.Parameters.AddWithValue("@address2", address2);
command.Parameters.AddWithValue("@city", city);
command.Parameters.AddWithValue("@state", state);
command.Parameters.AddWithValue("@zip", zip);
command.Parameters.AddWithValue("@nametype", nametype);
command.Parameters.AddWithValue("@programyear", year);
command.Parameters.AddWithValue("@stateid", stateid);
command.Parameters.AddWithValue("@countycode", countycode);
command.Parameters.AddWithValue("@farmnumber", farmnumber);
command.Parameters.AddWithValue("@tractnumber", tractnumber);
command.Parameters.AddWithValue("@fieldid", fieldid);
command.Parameters.AddWithValue("@fieldnumber", fieldnumber);
command.Parameters.AddWithValue("@cropcode", cropcode);
command.Parameters.AddWithValue("@croptypecode", croptypecode);
command.Parameters.AddWithValue("@cropintendedusecode", cropintendedusecode);
command.Parameters.AddWithValue("@irricode", irricode);
command.Parameters.AddWithValue("@plantprimcode", plantprimcode);
command.Parameters.AddWithValue("@plantsecode", plantseccode);
command.Parameters.AddWithValue("@plantmulticode", plantmulticode);
command.Parameters.AddWithValue("@croplandindicator", croplandindicator);
command.Parameters.AddWithValue("@croprep", cropfieldrep);
command.Parameters.AddWithValue("@cropdeterim", cropdeterim);
command.Parameters.AddWithValue("@lastdate", DateTime.Now);
command.Parameters.AddWithValue("@customernumber", customernumber);
command.Parameters.AddWithValue("@businesspartycode", businesspartycode);
command.Parameters.AddWithValue("@sharepercentage", cropsharepercentage);
command.Parameters.AddWithValue("@settlementid", settlementid);
command.Parameters.AddWithValue("@processeddate", DBNull.Value);
db.ManipulateRecord(connectionString, connection, command, query);
}
}
break;
case "RMA":
if (dicPairs.ContainsKey(cid))
{
cidTType = dicPairs[cid];
if (cidTType.Contains(transfertypeId))
{
transferDataId = (int)ht[transfertypeId];
query = "Insert Into dbo.tblRMAResponseDetail (TransferDataID, CommodityYear, TaxID, TaxType, " +
"BGFirstName, BGMiddleName, BGLastName, BGBusinessName," +
"FirstName, MiddleName, LastName, BusinessName, FormattedName," +
"Address1, Address2, City, State, ZipCode, EntityTypeCode, " +
"LocationStateCode, LocationStateAbbrev, LocationCountyCode, LocationCountyName, " +
"CommodityCode, TypeCode, PracticeCode, InsuredSharePercent, LandTypeCode, " +
"FarmNumber, TractNumber, fieldnumber, LandID, RLUID, CLUID," +
"ReportedAcreage, SettlementID, ProcessedDate)" +
"VALUES(@TransferDataID, @commodityyear, @taxID, @taxType, " +
"@bgfirstname, @bgmiddlename, @bglastname, @bgbusinessname, " +
"@firstname, @middlename, @lastname, @businessname, @formatname, " +
"@address1, @address2, @city, @state, @zipcode, @entitytypecode, " +
"@locationstatecode, @locationstateabbrev, @locationcountycode, @locationcountyname, " +
"@commoditycode, @typecode, @practicecode, @insuredsharedpercent, @landtypecode, " +
"@farmnumber, @tractnumber, @fieldnumber, @landid, @rluid, @cluid, " +
"@reportedacreage, @settlementid, @processeddate)";
command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@TransferDataID", transferDataId);
command.Parameters.AddWithValue("@commodityyear", year);
command.Parameters.AddWithValue("@taxID", taxInfo[0]);
command.Parameters.AddWithValue("@taxType", taxInfo[1]);
command.Parameters.AddWithValue("@bgfirstname", bgfirstname);
command.Parameters.AddWithValue("@bgmiddlename", bgmiddlename);
command.Parameters.AddWithValue("@bglastname", bglastname);
command.Parameters.AddWithValue("@bgbusinessname", bgbusinessname);
command.Parameters.AddWithValue("@firstname", fname);
command.Parameters.AddWithValue("@middlename", mname);
command.Parameters.AddWithValue("@lastname", lname);
command.Parameters.AddWithValue("@businessname", businessname);
command.Parameters.AddWithValue("@formatname", formatname);
command.Parameters.AddWithValue("@address1", address1);
command.Parameters.AddWithValue("@address2", address2);
command.Parameters.AddWithValue("@city", city);
command.Parameters.AddWithValue("@state", state);
command.Parameters.AddWithValue("@zipcode", zip);
command.Parameters.AddWithValue("@entitytypecode", entitytypecode);
command.Parameters.AddWithValue("@locationstatecode", stateid);
command.Parameters.AddWithValue("@locationstateabbrev", locationstateabbrev);
command.Parameters.AddWithValue("@locationcountycode", countycode);
command.Parameters.AddWithValue("@locationcountyname", locationcountyname);
command.Parameters.AddWithValue("@commoditycode", commoditycode);
command.Parameters.AddWithValue("@typecode", typecode);
command.Parameters.AddWithValue("@practicecode", practicecode);
command.Parameters.AddWithValue("@insuredsharedpercent", cropsharepercentage);
command.Parameters.AddWithValue("@landtypecode", landtypecode);
command.Parameters.AddWithValue("@farmnumber", farmnumber);
command.Parameters.AddWithValue("@tractnumber", tractnumber);
command.Parameters.AddWithValue("@fieldnumber", fieldnumber);
command.Parameters.AddWithValue("@landid", landid);
command.Parameters.AddWithValue("@rluid", rluid);
command.Parameters.AddWithValue("@cluid", cluid);
command.Parameters.AddWithValue("@reportedacreage", cropfieldrep);
command.Parameters.AddWithValue("@settlementid", settlementid);
command.Parameters.AddWithValue("@processeddate", DBNull.Value);
db.ManipulateRecord(connectionString, connection, command, query);
}
}
break;
}
}
public void InsertIntoOptOutTableForProd(string optoutId, string xmlHistId, string recordNo, string admin, string attaddr1, string attaddr2, string attcity, string attFirst, string attLast, string attMiddle, string attPhone1, string attPhone2, string attPhone3, string attState, string attSuffix, string attZip, string business, string caAcres13, string caAcres13NP, string caAcres14, string caAcres14NP, string caAcres15, string caAcres15NP, string caAcres16, string caAcres16NP, string caAcres17, string caAcres17NP, string caAcresTotal, string child, string city, string cmem13, string CMem13NP, string CMem14, string CMem14NP, string CMem15, string CMem15NP, string CMem16, string CMem16NP, string CMem17, string CMem17NP, string CMemShare, string CMemShareNP, string CPhone1, string CPhone2, string CPhone3, string Day, string Deceased, string EIN, string Executor, string FarmAddress1, string FarmAddress2, string FarmCity, string FarmPhone1, string FarmPhone2, string FarmPhone3, string FarmState, string FarmZip, string FirmName, string First, string GovRecNo, string GovRecYes, string Last, string LegallyInCap, string Middle, string Minor, string Month, string OptOutInc, string OptOutNo, string OptOutYes, string Parent, string PerYear, string RelOther, string RelOtherDesc, string RepEIN, string RepFirst, string RepLast, string RepMiddle, string RepOther, string RepOtherDesc, string RepSSN, string RepSuffix, string RepTaxpayerID, string Sibling, string Signature, string SignHandWritten, string SignNotProvided, string Spouse, string SSN, string StateProvince, string StreetAddress1, string StreetAddress2, string Suffix, string TaxPayerID, string Total, string Year, string ZipCode)
{
string query = "";
query = "INSERT INTO [dbo].[tblFillableOptOutData_Prod] ([OptOutID] " +
" ,[XMLHistID] " +
" ,[RecordNo] " +
" ,[Administrator] " +
" ,[AttAddress1] " +
" ,[AttAddress2] " +
" ,[AttCity] " +
" ,[AttFirst] " +
" ,[AttLast] " +
" ,[AttMiddle] " +
" ,[AttPhone1] " +
" ,[AttPhone2] " +
" ,[AttPhone3] " +
" ,[AttState] " +
" ,[AttSuffix] " +
" ,[AttZip] " +
" ,[BusinessName] " +
" ,[CAcres13] " +
" ,[CAcres13NP] " +
" ,[CAcres14] " +
" ,[CAcres14NP] " +
" ,[CAcres15] " +
" ,[CAcres15NP] " +
" ,[CAcres16] " +
" ,[CAcres16NP] " +
" ,[CAcres17] " +
" ,[CAcres17NP] " +
" ,[CAcresTotal] " +
" ,[Child] " +
" ,[City] " +
" ,[CMem13] " +
" ,[CMem15NP] " +
" ,[CMem14] " +
" ,[CMem14NP] " +
" ,[CMem15] " +
" ,[CMem16] " +
" ,[CMem16NP] " +
" ,[CMem17] " +
" ,[CMem17NP] " +
" ,[CMem13NP] " +
" ,[CMemShare] " +
" ,[CMemShareNP] " +
" ,[CPhone1] " +
" ,[CPhone2] " +
" ,[CPhone3] " +
" ,[Day] " +
" ,[Deceased] " +
" ,[EIN] " +
" ,[Executor] " +
" ,[FarmAddress1] " +
" ,[FarmAddress2] " +
" ,[FarmCity] " +
" ,[FarmPhone1] " +
" ,[FarmPhone2] " +
" ,[FarmPhone3] " +
" ,[FarmState] " +
" ,[FarmZip] " +
" ,[FirmName] " +
" ,[First] " +
" ,[GovRecNo] " +
" ,[GovRecYes] " +
" ,[Last] " +
" ,[LegallyInCap] " +
" ,[Middle] " +
" ,[Minor] " +
" ,[Month] " +
" ,[OptOutInc] " +
" ,[OptOutNo] " +
" ,[OptOutYes] " +
" ,[Parent] " +
" ,[PerYear] " +
" ,[RelOther] " +
" ,[RelOtherDesc] " +
" ,[RepEIN] " +
" ,[RepFirst] " +
" ,[RepLast] " +
" ,[RepMiddle] " +
" ,[RepOther] " +
" ,[RepOtherDesc] " +
" ,[RepSSN] " +
" ,[RepSuffix] " +
" ,[RepTaxpayerID] " +
" ,[Sibling] " +
" ,[Signature] " +
" ,[SignHandWritten] " +
" ,[SignNotProvided] " +
" ,[Spouse] " +
" ,[SSN] " +
" ,[StateProvince] " +
" ,[StreetAddress1] " +
" ,[StreetAddress2] " +
" ,[Suffix] " +
" ,[TaxPayerID] " +
" ,[Total] " +
" ,[Year] " +
" ,[ZipCode]) " +
" VALUES " +
"(@OptOutID " +
",@XMLHistID " +
",@RecordNo " +
",@Administrator " +
",@AttAddress1 " +
",@AttAddress2 " +
",@AttCity " +
",@AttFirst " +
",@AttLast " +
",@AttMiddle " +
",@AttPhone1 " +
",@AttPhone2 " +
",@AttPhone3 " +
",@AttState " +
",@AttSuffix " +
",@AttZip " +
",@BusinessName " +
",@CAcres13 " +
",@CAcres13NP " +
",@CAcres14 " +
",@CAcres14NP " +
",@CAcres15 " +
",@CAcres15NP " +
",@CAcres16 " +
",@CAcres16NP " +
",@CAcres17 " +
",@CAcres17NP " +
",@CAcresTotal " +
",@Child " +
",@City " +
",@CMem13 " +
",@CMem15NP " +
",@CMem14 " +
",@CMem14NP " +
",@CMem15 " +
",@CMem16 " +
",@CMem16NP " +
",@CMem17 " +
",@CMem17NP " +
",@CMem13NP " +
",@CMemShare " +
",@CMemShareNP " +
",@CPhone1 " +
",@CPhone2 " +
",@CPhone3 " +
",@Day " +
",@Deceased " +
",@EIN " +
",@Executor " +
",@FarmAddress1 " +
",@FarmAddress2 " +
",@FarmCity " +
",@FarmPhone1 " +
",@FarmPhone2 " +
",@FarmPhone3 " +
",@FarmState " +
",@FarmZip " +
",@FirmName " +
",@First " +
",@GovRecNo " +
",@GovRecYes " +
",@Last " +
",@LegallyInCap " +
",@Middle " +
",@Minor " +
",@Month " +
",@OptOutInc " +
",@OptOutNo " +
",@OptOutYes " +
",@Parent " +
",@PerYear " +
",@RelOther " +
",@RelOtherDesc " +
",@RepEIN " +
",@RepFirst " +
",@RepLast " +
",@RepMiddle " +
",@RepOther " +
",@RepOtherDesc " +
",@RepSSN " +
",@RepSuffix " +
",@RepTaxpayerID " +
",@Sibling " +
",@Signature " +
",@SignHandWritten " +
",@SignNotProvided " +
",@Spouse " +
",@SSN " +
",@StateProvince " +
",@StreetAddress1 " +
",@StreetAddress2 " +
",@Suffix " +
",@TaxPayerID " +
",@Total " +
",@Year " +
",@ZipCode)";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@OptOutID", Int32.Parse(optoutId));
command.Parameters.AddWithValue("@XMLHistID", Int32.Parse(xmlHistId));
command.Parameters.AddWithValue("@RecordNo", Int32.Parse(recordNo));
command.Parameters.AddWithValue("@Administrator", admin);
command.Parameters.AddWithValue("@AttAddress1", attaddr1);
command.Parameters.AddWithValue("@AttAddress2", attaddr2);
command.Parameters.AddWithValue("@AttCity", attcity);
command.Parameters.AddWithValue("@AttFirst", attFirst);
command.Parameters.AddWithValue("@AttLast", attLast);
command.Parameters.AddWithValue("@AttMiddle", attMiddle);
command.Parameters.AddWithValue("@AttPhone1", attPhone1);
command.Parameters.AddWithValue("@AttPhone2", attPhone2);
command.Parameters.AddWithValue("@AttPhone3", attPhone3);
command.Parameters.AddWithValue("@AttState", attState);
command.Parameters.AddWithValue("@AttSuffix", attSuffix);
command.Parameters.AddWithValue("@AttZip", attZip);
command.Parameters.AddWithValue("@BusinessName", business);
command.Parameters.AddWithValue("@CAcres13", caAcres13);
command.Parameters.AddWithValue("@CAcres13NP", caAcres13NP);
command.Parameters.AddWithValue("@CAcres14", caAcres14);
command.Parameters.AddWithValue("@CAcres14NP", caAcres14NP);
command.Parameters.AddWithValue("@CAcres15 ", caAcres15);
command.Parameters.AddWithValue("@CAcres15NP", caAcres15NP);
command.Parameters.AddWithValue("@CAcres16", caAcres16);
command.Parameters.AddWithValue("@CAcres16NP", caAcres16NP);
command.Parameters.AddWithValue("@CAcres17", caAcres17);
command.Parameters.AddWithValue("@CAcres17NP", caAcres17NP);
command.Parameters.AddWithValue("@CAcresTotal", caAcresTotal);
command.Parameters.AddWithValue("@Child", child);
command.Parameters.AddWithValue("@City", city);
command.Parameters.AddWithValue("@CMem13", cmem13);
command.Parameters.AddWithValue("@CMem13NP", CMem13NP);
command.Parameters.AddWithValue("@CMem14", CMem14);
command.Parameters.AddWithValue("@CMem14NP", CMem14NP);
command.Parameters.AddWithValue("@CMem15", CMem15);
command.Parameters.AddWithValue("@CMem15NP", CMem15NP);
command.Parameters.AddWithValue("@CMem16", CMem16);
command.Parameters.AddWithValue("@CMem16NP", CMem16NP);
command.Parameters.AddWithValue("@CMem17", CMem17);
command.Parameters.AddWithValue("@CMem17NP", CMem17NP);
command.Parameters.AddWithValue("@CMemShare ", CMemShare);
command.Parameters.AddWithValue("@CMemShareNP", CMemShareNP);
command.Parameters.AddWithValue("@CPhone1 ", CPhone1);
command.Parameters.AddWithValue("@CPhone2 ", CPhone2);
command.Parameters.AddWithValue("@CPhone3 ", CPhone3);
command.Parameters.AddWithValue("@Day", Day);
command.Parameters.AddWithValue("@Deceased", Deceased);
command.Parameters.AddWithValue("@EIN", EIN);
command.Parameters.AddWithValue("@Executor", Executor);
command.Parameters.AddWithValue("@FarmAddress1", FarmAddress1);
command.Parameters.AddWithValue("@FarmAddress2", FarmAddress2);
command.Parameters.AddWithValue("@FarmCity", FarmCity);
command.Parameters.AddWithValue("@FarmPhone1", FarmPhone1);
command.Parameters.AddWithValue("@FarmPhone2", FarmPhone2);
command.Parameters.AddWithValue("@FarmPhone3", FarmPhone3);
command.Parameters.AddWithValue("@FarmState ", FarmState);
command.Parameters.AddWithValue("@FarmZip", FarmZip);
command.Parameters.AddWithValue("@FirmName", FirmName);
command.Parameters.AddWithValue("@First", First);
command.Parameters.AddWithValue("@GovRecNo", GovRecNo);
command.Parameters.AddWithValue("@GovRecYes", GovRecNo);
command.Parameters.AddWithValue("@Last", Last);
command.Parameters.AddWithValue("@LegallyInCap", LegallyInCap);
command.Parameters.AddWithValue("@Middle", Middle);
command.Parameters.AddWithValue("@Minor", Minor);
command.Parameters.AddWithValue("@Month", Month);
command.Parameters.AddWithValue("@OptOutInc", OptOutInc);
command.Parameters.AddWithValue("@OptOutNo", OptOutNo);
command.Parameters.AddWithValue("@OptOutYes", OptOutYes);
command.Parameters.AddWithValue("@Parent", Parent);
command.Parameters.AddWithValue("@PerYear", PerYear);
command.Parameters.AddWithValue("@RelOther", RelOther);
command.Parameters.AddWithValue("@RelOtherDesc", RelOtherDesc);
command.Parameters.AddWithValue("@RepEIN", RepEIN);
command.Parameters.AddWithValue("@RepFirst", RepFirst);
command.Parameters.AddWithValue("@RepLast", RepLast);
command.Parameters.AddWithValue("@RepMiddle", RepMiddle);
command.Parameters.AddWithValue("@RepOther", RepOther);
command.Parameters.AddWithValue("@RepOtherDesc", RepOtherDesc);
command.Parameters.AddWithValue("@RepSSN", RepSSN);
command.Parameters.AddWithValue("@RepSuffix", RepSuffix);
command.Parameters.AddWithValue("@RepTaxpayerID ", RepTaxpayerID);
command.Parameters.AddWithValue("@Sibling", Sibling);
command.Parameters.AddWithValue("@Signature", Signature);
command.Parameters.AddWithValue("@SignHandWritten", SignHandWritten);
command.Parameters.AddWithValue("@SignNotProvided", SignNotProvided);
command.Parameters.AddWithValue("@Spouse", Spouse);
command.Parameters.AddWithValue("@SSN", SSN);
command.Parameters.AddWithValue("@StateProvince", StateProvince);
command.Parameters.AddWithValue("@StreetAddress1 ", StreetAddress1);
command.Parameters.AddWithValue("@StreetAddress2 ", StreetAddress2);
command.Parameters.AddWithValue("@Suffix", Suffix);
command.Parameters.AddWithValue("@TaxPayerID", TaxPayerID);
command.Parameters.AddWithValue("@Total", Total);
command.Parameters.AddWithValue("@Year", Year);
command.Parameters.AddWithValue("@ZipCode", ZipCode);
db.ManipulateRecord(connectionString, connection, command, query);
}