/* * procAccounts - by range, single or allfrom SQL - fill the passed-in Cust list box with the names of processed accounts and * License lst box with processed licenses */ public static void procAccounts(int searchtype, string clientid1, string clientid2, ListBox lstCustBox, ListBox lstLicBox, bool test, bool custonly) { lstCustBox.Items.Clear(); lstLicBox.Items.Clear(); string AddCustResult = ""; bool SkipError = false; string newCustID = ""; //Set up file names to send results // Set a variable to the Documents path. string docPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments); //create file name DateTime d = DateTime.Now; string dateString = d.ToString("yyyyMMddHHmmss"); string custfilename = "customers-" + dateString + ".txt"; string custfullpath = Path.Combine(docPath, custfilename); string licfilename = "licenses-" + dateString + ".txt"; string licfullpath = Path.Combine(docPath, licfilename); // Write header line to a new results files string custfiletext = "AcctName \t ClientID: \t Add Result \t LicResult" + Environment.NewLine; File.WriteAllText(custfullpath, custfiletext); string licfiletext = "AcctName \t clientID \t SerialNum \t LicType \t CustomerID \t AddLicResult \t LicID \t LicPW \t AddLicNotes" + Environment.NewLine; File.WriteAllText(licfullpath, licfiletext); // Open connection to SQL Server string connectionString = ConfigurationManager.ConnectionStrings["NetlibApi1.Properties.Settings.custdb"].ConnectionString; SqlConnection dbConAcct = new SqlConnection(connectionString); // "Server= 192.168.86.33;Database=SWKExchange;User Id=swkdata;Password=MollyLinus;" dbConAcct.Open(); // Command DbCommand dbCmdAcct = dbConAcct.CreateCommand(); // search all if (searchtype == 1) { dbCmdAcct.CommandText = "select * from AcctContact order by AccountID"; } else //search single if (searchtype == 2) { dbCmdAcct.CommandText = "select * from AcctContact"; dbCmdAcct.CommandText += " where ClientID = '" + clientid1 + "'"; dbCmdAcct.CommandText += " order by ClientID"; } else //search Range { dbCmdAcct.CommandText = "select * from AcctContact"; dbCmdAcct.CommandText += " where ClientID BETWEEN '" + clientid1 + "' and '" + clientid2 + "'"; dbCmdAcct.CommandText += " order by ClientID"; } // Get the data DbDataReader rsDataAcct = dbCmdAcct.ExecuteReader(); if (rsDataAcct.HasRows) { while (rsDataAcct.Read()) { string AcctName = rsDataAcct["AccountName"].ToString(); string clientID = rsDataAcct["ClientID"].ToString(); string Acctid = rsDataAcct["AccountID"].ToString(); // Has this customer been added to Soloserver previously string SWCustomerID = rsDataAcct["SWCustomerID"].ToString(); if (SWCustomerID == "") { // add Account to SoloServer string firstName = rsDataAcct["FirstName"].ToString(); string lastName = rsDataAcct["LastName"].ToString(); string address1 = rsDataAcct["AddressLine1"].ToString(); string address2 = rsDataAcct["AddressLine2"].ToString(); string city = rsDataAcct["City"].ToString(); string state = rsDataAcct["StateProvince"].ToString(); string zip = rsDataAcct["PostalCode"].ToString(); string country = rsDataAcct["Country"].ToString(); string email = rsDataAcct["Email"].ToString(); string pwd = rsDataAcct["dnld_userpw"].ToString(); string nickname = "GMClientID: " + rsDataAcct["GMClientID"].ToString(); int newId = ApiAccess.addSWKCustomer(AcctName, firstName, lastName, address1, address2, city, state, zip, country, email, pwd, "", "", nickname); if (newId < 0) { // Error with SWK add AddCustResult = "Add Error: " + newId.ToString(); SkipError = true; } else { // successful SWK add newCustID = newId.ToString(); AddCustResult = "Added: " + newCustID + "pw: " + pwd; // update Account record in SQL with SWK data string updResult = DataAccess.updateCustSWKData(Acctid, newCustID, pwd, email, "Insert"); AddCustResult += " " + updResult; } } else { // has been previously added according to Account Record // confirm customer on SoloServer and then process licenses. newCustID = SWCustomerID; AddCustResult = "Prev Add: " + newCustID; } if (custonly) { SkipError = true; } string licResult = ""; if (SkipError) { // dont process licenses licResult = "Skipped Licenses"; } else { // get licenses for this AcctID int licensesProcessed; licensesProcessed = ImportData.procLicenses(Acctid, newCustID, AcctName, clientID, lstLicBox, licfullpath, test); licResult = "Lic: " + licensesProcessed.ToString(); } //list customers processed in box and results file custfiletext = AcctName + " \t ClientID: " + clientID + " \t Add Result: " + AddCustResult + " \t " + licResult; lstCustBox.Items.Add(custfiletext); File.AppendAllText(custfullpath, custfiletext + Environment.NewLine); } } // Close Connection dbConAcct.Close(); }
/* * procLicenses - This procedure reads Serial Numbers from SQL for selected AcctID and calls routine to add * to Solo Server */ public static int procLicenses(string AcctID, string CustomerID, string AcctName, string clientID, ListBox lstLicBox, string licfullpath, bool testLic) { /* * This procedure reads Serial Numbers from SQL for selected AcctID and calls routine to add * to Solo Server */ //lstLicBox.Items.Clear(); string AddLicResult = ""; bool SkipError = false; // Open connection to SQL Server string connectionString = ConfigurationManager.ConnectionStrings["NetlibApi1.Properties.Settings.custdb"].ConnectionString; SqlConnection dbConSN = new SqlConnection(connectionString); // "Server= 192.168.86.33;Database=SWKExchange;User Id=swkdata;Password=MollyLinus;" dbConSN.Open(); // Command DbCommand dbCmdSN = dbConSN.CreateCommand(); dbCmdSN.CommandText = "select * from SerialNumbers where AccountID = @id "; dbCmdSN.CommandText += "order by SerialNumber, LicType desc"; SqlParameter custParam = new SqlParameter("@id", AcctID); dbCmdSN.Parameters.Add(custParam); // Get the data DbDataReader rsDataSN = dbCmdSN.ExecuteReader(); int recordcounter = 0; if (rsDataSN.HasRows) { while (rsDataSN.Read()) { //assign sql data to variables // string AcctName = rsDataSN["AccountName"].ToString(); // string clientID = rsDataSN["ClientID"].ToString(); string SerialNum = rsDataSN["SerialNumber"].ToString(); string LicType = rsDataSN["LicType"].ToString(); // Has this License been added to Soloserver previously string SWLicID = rsDataSN["SWLicenseID"].ToString(); string SWLicPW = rsDataSN["SWLicensePW"].ToString(); if (SWLicID == "") { // add License to SoloServer string optionID = rsDataSN["ProdOptionID"].ToString(); string qty = rsDataSN["Quantity"].ToString(); string purchased = rsDataSN["DatePurchased"].ToString(); string expire = rsDataSN["SuppExpireDate"].ToString(); string activation = rsDataSN["ActivationCount"].ToString(); string deactivation = rsDataSN["DeactivationCount"].ToString(); string cores = rsDataSN["cores"].ToString(); string notes = rsDataSN["Notes"].ToString(); //string acctid = rsDataSN["AcctID"].ToString(); string SNID = rsDataSN["SerialNumberID"].ToString(); string LicName = rsDataSN["LicenseeName"].ToString(); string CData = rsDataSN["CustomData"].ToString(); int i = 0; int loops = 0; int qtylic = 0; string newLicID = ""; string newLicPwd = ""; string newLicResult = ""; // determine the number of licenses to add based on qty // but have to convert to int bool isParsable = Int32.TryParse(qty, out qtylic); if (isParsable) { // loops are set to output loops = qtylic; qty = "1"; } else { loops = 1; } while (i < loops) { //addLicense function using Tuple that will return 3 values - new License ID, Lic Activation Password, result message var LicIDPwd = ImportData.addLicense(lstLicBox, CustomerID, optionID, qty, purchased, expire, activation, deactivation, cores, notes, SerialNum, SNID, LicName, CData, LicType, testLic); newLicID = LicIDPwd.Item1; newLicPwd = LicIDPwd.Item2; newLicResult = LicIDPwd.Item3; i++; } if (newLicID == "") { // Error with SWK add AddLicResult = "Add Lic Error: " + newLicResult; SkipError = true; } else { // successful SWK add, split up res AddLicResult = "Added: \t " + newLicID + " \t PW: " + newLicPwd + " \t " + newLicResult; // update Account record in SQL with SWK data // updateLicSWKData(string SerialNumId, string newSWId, string newSWPwd, string UpdMode) string updResult = DataAccess.updateLicSWKData(SNID, LicType, newLicID, newLicPwd, "Insert"); AddLicResult += " " + updResult; } } else { // has been previously added according to Account Record // confirm customer on SoloServer and then process licenses. AddLicResult = "Exists:\t " + SWLicID + " \t PW: " + SWLicPW; } //add items to list and results file string licfiletext = AcctName + " \t " + clientID + " \t " + SerialNum + " \t " + LicType + " \t " + CustomerID + " \t " + AddLicResult; lstLicBox.Items.Add(licfiletext); File.AppendAllText(licfullpath, licfiletext + Environment.NewLine); recordcounter = recordcounter + 1; } } rsDataSN.Close(); // Close Connection dbConSN.Close(); // returns number of licenses processed for the Account return(recordcounter); }