I don't know much about how to call stored procedures from dc and used a typical database call.
1. create a config table
configKey : varchar(50);
configValue: varchar(50)
configKey configValue
'MaxOrderNumberLength' 11
'lastordernumber' 0
2. create a stored procedure
CREATE proc [dbo].[dashCommerce_Store_FetchNextOrderNumber] @strNextOrderNumber varchar(50) OUT
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(101) -- inlcuding 50 zeros at max plus '-'.
DECLARE @intLength int
SELECT @intLength = CAST(configValue as int) -- get a Length of order number.FROM dbo.dashCommerce_Store_Config WHERE configKey = 'MaxOrderNumberLength'
if @intLength <11 -- must be equal to or longer than 11
SET @intLength = 11;
if @intLength > 50 -- must be less than or equal to 50.
SET @intLength = 50;
Declare @intOrderNumber int
SELECT @intOrderNumber = CAST( ConfigValue AS int) + 1 -- get the last order number used and add one to it FROM dbo.dashCommerce_Store_Config
WHERE ConfigKey = 'lastordernumber';
-- Make the integer order number to string.
SET @strNextOrderNumber = Cast(@intOrderNumber AS varchar(50))
-- 7 means the length of 'YYMMDD-' which is a prefix of order numbers. eg. 080616-00000001
IF LEN(@strNextOrderNumber ) >= @intLength -7 -- if meaningful number length is longer than exprected, then do not remoev any.
SELECT @Result = @strNextOrderNumber;
ELSE
BEGIN
SELECT @Result = '0000000000000000000000000000000000000000000000000' + RTRIM(@strNextOrderNumber);
SELECT @Result = Right (@Result, @intLength -7);
END
SELECT @Result = convert(varchar(6), getdate(), 12 ) + '-' + @Result;
-- SET the last order number back to config table.UPDATE dbo.dashCommerce_Store_Config SET ConfigValue = @strNextOrderNumber
WHERE ConfigKey = 'lastordernumber';
SELECT @strNextOrderNumber = LEFT(@Result, 50) -- RETURN only first 50.
END
3. Modify the core.coreUtility.GenerateRandomString()
public static string GenerateRandomString(int length)
{
//order number will be "YYMMDD-000123456"; //some shoppers just add some to a cart and left. But still looks good because the number keeps increased
// for instance, 080616-00101 seems to be looking better than just 080616-00002 with one purchase in a day.
string nextOrderNumber = "";
SqlConnection con;SqlCommand cmd;
try
{
string strConnectionString = "Server=localhost;Initial Catalog=csk3r;User Id=xxxxx;Password=xxxxxx;";con = new SqlConnection(strConnectionString);
con.Open();
cmd =
new SqlCommand("[dbo].[dashCommerce_Store_FetchNextOrderNumber]", con);cmd.CommandType = CommandType.StoredProcedure;SqlParameter prm = new SqlParameter("@strNextOrderNumber", SqlDbType.VarChar);
prm.Size=50;
prm.Direction = ParameterDirection.Output;
cmd.Parameters.Add (prm);
cmd.ExecuteNonQuery();
nextOrderNumber = cmd.Parameters["@strNextOrderNumber"].Value.ToString();
return nextOrderNumber;
}
catch (Exception e)
{
throw;
}
finally
{
cmd = null;con = null;
}
}