>>>>I guessed when you originally posted, that you didn't have a working solution. You asked a question, we're given an answer, threw out the answer and went your own way. If you don't want to discuss the options, don't ask the question.
>>>
>>>I am thinking that for the next part of this method I am converting from VFP into C# I do need to create two stored procedures.
>>
>>If you are going to use a SP then you may as well put all the logic there.....
>
>This is how the whole procedure now looks like - I accept suggestions and critique. At the end I decided to use your suggestion for the first part and I also created 3 new SPs last night. Today I am going to write more unit tests and try to convert few more methods.
>
>
>/// <summary>
> /// Ends a selling session and optionally accepts amounts collected and stock #s
> /// </summary>
> /// <param name="parameters"></param>
> /// <returns></returns>
> internal static String CloseDrawer(Dictionary<String, String> parameters)
> {
> Logging.Log(1, "Entering CloseDrawer");
>
> List<String> requiredParameters = new List<String>() { "tcDetails" };
> //always required parameters { "tcSalesPoint", "tcOperator", "tcReturnType" };
> //optional parameters {tcNoClose};
>
> String messageText = "";
> Int32 errorValue = 0;
> Int32 statusCode = 0;
>
> // If all necessary parameters are present and valid, proceed...
> if (Functions.CheckRequiredParameters(parameters, requiredParameters, null, out statusCode, out messageText))
> {
> try
> {
> SqlCommand sqlCommand = new SqlCommand();
>
> sqlCommand.CommandType = CommandType.Text;
> sqlCommand.CommandText = "select closedrwrs from dbo.prefs_sl;";
>
> if (Base.ExecuteSqlCommand(sqlCommand, ref messageText, ref statusCode))
> {
> Int16 closeDrawers;
> if (Int16.TryParse(messageText, out closeDrawers))
> {
> if (closeDrawers < 2)
> {
> statusCode = 610;
> messageText = "System is not set up to close cash drawers";
> }
> // Ok to close a drawer
> else
> {
> sqlCommand.Parameters.Clear();
> String currentOperator, currentSalespoint, closeOperator, closeSalespoint, cDetails;
> currentOperator = Functions.GetParameterValue(parameters, "tcOperator");
> currentSalespoint = Functions.GetParameterValue(parameters, "tcSalespoint");
> cDetails = Functions.GetParameterValue(parameters, "tcDetails");
> closeOperator = (cDetails.ParseStringFromSqml("CLOSE_OP")) ?? currentOperator;
> closeSalespoint = (cDetails.ParseStringFromSqml("CLOSE_SP")) ?? currentSalespoint;
> DateTime startTime, endTime;
> startTime = cDetails.ParseDateTimeFromSqml("START_TIME");
> endTime = cDetails.ParseDateTimeFromSqml("END_TIME");
> String closeType;
>
> StringBuilder whereClause = new StringBuilder();
> if (closeDrawers.InList<short>(2, 4))
> {
> DateTime defaultDate = Siriusware.Library.Functions.DTSqlMinDate;
> closeType = "BySalespoint";
> sqlCommand.Parameters.Add("@CloseSalespoint", SqlDbType.Char, 6).Value = closeSalespoint.PadRight(6);
> if (endTime == defaultDate) endTime = DateTime.MaxValue;
> whereClause.AppendLine("salespoint = @CloseSalesPoint AND date_time between @StartTime and @EndTime");
> sqlCommand.Parameters.Add("@StartTime", SqlDbType.DateTime).Value = startTime;
> sqlCommand.Parameters.Add("@EndTime", SqlDbType.DateTime).Value = endTime;
> }
> else
> {
> closeType = "ByOperator";
> whereClause.AppendLine("operator = @CloseOperator");
> sqlCommand.Parameters.Add("@CloseOperator", SqlDbType.Char, 6).Value = closeOperator.PadRight(6);
> }
>
> whereClause.AppendLine(" AND closeout = 0;");
>
> sqlCommand.CommandText = "SELECT count(*) as salecount FROM dbo.sale_hdr WHERE " + whereClause.ToString();
>
> if (Base.ExecuteSqlCommand(sqlCommand, ref messageText, ref statusCode))
> {
> Int32 salesCount;
> if (Int32.TryParse(messageText, out salesCount))
> {
> if (0 == salesCount)
> {
> statusCode = 330;
> messageText = "There are no sales to close out";
> }
> else
> {
> String noClose;
> noClose = Functions.GetParameterValue(parameters, "tcNoClose");
> if (!String.IsNullOrWhiteSpace(noClose) && noClose.ToUpper().InList("Y", "T", "YES", "TRUE"))
> {
> statusCode = 0;
> messageText = String.Format("{0} sales to close out", salesCount);
> }
> else
> {
> // Perform the closeout
> Int32 stock_t1_s1, stock_t1_e1, stock_t1_s2, stock_t1_e2, stock_t1_s3, stock_t1_e3;
> Int32 stock_t2_s1, stock_t2_e1, stock_t2_s2, stock_t2_e2, stock_t2_s3, stock_t2_e3;
> Int32 stock_v1_s1, stock_v1_e1, stock_v1_s2, stock_v1_e2, stock_v1_s3, stock_v1_e3;
> Int32 stock_v2_s1, stock_v2_e1, stock_v2_s2, stock_v2_e2, stock_v2_s3, stock_v2_e3;
>
> stock_t1_s1 = cDetails.ParseIntFromSqml("STOCK_T1_S1");
> stock_t1_e1 = cDetails.ParseIntFromSqml("STOCK_T1_E1");
> stock_t1_s2 = cDetails.ParseIntFromSqml("STOCK_T1_S2");
> stock_t1_e2 = cDetails.ParseIntFromSqml("STOCK_T1_E2");
> stock_t1_s3 = cDetails.ParseIntFromSqml("STOCK_T1_S3");
> stock_t1_e3 = cDetails.ParseIntFromSqml("STOCK_T1_E3");
>
> stock_t2_s1 = cDetails.ParseIntFromSqml("STOCK_t2_S1");
> stock_t2_e1 = cDetails.ParseIntFromSqml("STOCK_t2_E1");
> stock_t2_s2 = cDetails.ParseIntFromSqml("STOCK_t2_S2");
> stock_t2_e2 = cDetails.ParseIntFromSqml("STOCK_t2_E2");
> stock_t2_s3 = cDetails.ParseIntFromSqml("STOCK_t2_S3");
> stock_t2_e3 = cDetails.ParseIntFromSqml("STOCK_t2_E3");
>
> stock_v1_s1 = cDetails.ParseIntFromSqml("STOCK_v1_S1");
> stock_v1_e1 = cDetails.ParseIntFromSqml("STOCK_v1_E1");
> stock_v1_s2 = cDetails.ParseIntFromSqml("STOCK_v1_S2");
> stock_v1_e2 = cDetails.ParseIntFromSqml("STOCK_v1_E2");
> stock_v1_s3 = cDetails.ParseIntFromSqml("STOCK_v1_S3");
> stock_v1_e3 = cDetails.ParseIntFromSqml("STOCK_v1_E3");
>
> stock_v2_s1 = cDetails.ParseIntFromSqml("STOCK_v2_S1");
> stock_v2_e1 = cDetails.ParseIntFromSqml("STOCK_v2_E1");
> stock_v2_s2 = cDetails.ParseIntFromSqml("STOCK_v2_S2");
> stock_v2_e2 = cDetails.ParseIntFromSqml("STOCK_v2_E2");
> stock_v2_s3 = cDetails.ParseIntFromSqml("STOCK_v2_S3");
> stock_v2_e3 = cDetails.ParseIntFromSqml("STOCK_v2_E3");
>
> sqlCommand.CommandType = CommandType.StoredProcedure;
> sqlCommand.CommandText = "dbo.siriussp_CloseDrawer" + closeType;
>
> sqlCommand.Parameters.Add("@Details", SqlDbType.VarChar, -1).Value = cDetails;
> sqlCommand.Parameters.Add("@Salespoint", SqlDbType.Char, 6).Value = currentSalespoint;
> sqlCommand.Parameters.Add("@Operator", SqlDbType.Char, 6).Value = currentOperator;
> sqlCommand.Parameters.Add("@CurrentTime", SqlDbType.DateTime).Value = DateTime.Now;
>
> sqlCommand.Parameters.Add("@tkt1start1", SqlDbType.Int).Value = stock_t1_s1;
> sqlCommand.Parameters.Add("@tkt1end1", SqlDbType.Int).Value = stock_t1_e1;
> sqlCommand.Parameters.Add("@tkt1start2", SqlDbType.Int).Value = stock_t1_s2;
> sqlCommand.Parameters.Add("@tkt1end2", SqlDbType.Int).Value = stock_t1_e2;
> sqlCommand.Parameters.Add("@tkt1start3", SqlDbType.Int).Value = stock_t1_s3;
> sqlCommand.Parameters.Add("@tkt1end3", SqlDbType.Int).Value = stock_t1_e3;
>
> sqlCommand.Parameters.Add("@tkt2start1", SqlDbType.Int).Value = stock_t2_s1;
> sqlCommand.Parameters.Add("@tkt2end1", SqlDbType.Int).Value = stock_t2_e1;
> sqlCommand.Parameters.Add("@tkt2start2", SqlDbType.Int).Value = stock_t2_s2;
> sqlCommand.Parameters.Add("@tkt2end2", SqlDbType.Int).Value = stock_t2_e2;
> sqlCommand.Parameters.Add("@tkt2start3", SqlDbType.Int).Value = stock_t2_s3;
> sqlCommand.Parameters.Add("@tkt2end3", SqlDbType.Int).Value = stock_t2_e3;
>
> sqlCommand.Parameters.Add("@vch1start1", SqlDbType.Int).Value = stock_v1_s1;
> sqlCommand.Parameters.Add("@vch1end1", SqlDbType.Int).Value = stock_v1_e1;
> sqlCommand.Parameters.Add("@vch1start2", SqlDbType.Int).Value = stock_v1_s2;
> sqlCommand.Parameters.Add("@vch1end2", SqlDbType.Int).Value = stock_v1_e2;
> sqlCommand.Parameters.Add("@vch1start3", SqlDbType.Int).Value = stock_v1_s3;
> sqlCommand.Parameters.Add("@vch1end3", SqlDbType.Int).Value = stock_v1_e3;
>
> sqlCommand.Parameters.Add("@vch2start1", SqlDbType.Int).Value = stock_v2_s1;
> sqlCommand.Parameters.Add("@vch2end1", SqlDbType.Int).Value = stock_v2_e1;
> sqlCommand.Parameters.Add("@vch2start2", SqlDbType.Int).Value = stock_v2_s2;
> sqlCommand.Parameters.Add("@vch2end2", SqlDbType.Int).Value = stock_v2_e2;
> sqlCommand.Parameters.Add("@vch2start3", SqlDbType.Int).Value = stock_v2_s3;
> sqlCommand.Parameters.Add("@vch2end3", SqlDbType.Int).Value = stock_v2_e3;
> Logging.Log(5, "Executing " + sqlCommand.CommandText);
> Base.ExecuteSqlCommand(sqlCommand, ref messageText, ref statusCode);
> }
> }
> }
> }
>
> }
> }
> }
> else // Problem executing command
> statusCode = 400;
> }
>
> catch (Exception e)
> {
> statusCode = 400;
> messageText = e.ToString();
> }
>
> }
> String returnMessage = Base.GetReturnString(errorValue, statusCode, messageText);
> Logging.Log(returnMessage, 2);
>
> return returnMessage;
> }Hmm. I was just thinking that you could pass in the parameters 'as is' to a single SP and build up the query there based on the values passed (or not passed) but, at first glance, you might need 2 sp's - one for the 2,4 case and one for the other ? May not be worth it at this point....
