Hi friends,
i have a requirement in which i have a,
Field name PO No. & its format is like(DSCL-mm-yy / Kit 01)
in which DSCL is fixed
mm-stands for month
yy-stands for year
kit is the name of item,it is fixed
01 is the PO Number which will get incremented by 1 as the PO are generated.
my query is how to restart the PO number once the Current financial year(april 2012-march 2013) gets close and new begins.
how to get the following pattern
pl reply!
Regards,
Aamir
Rahul BhattPosted Jul 4, 2012, 1:20 AM
Your words some thing like
DSCL-01-2012 / Kit 01
DSCL-02-2012 / Kit 02
DSCL-03-2012 / Kit 03
DSCL-07-2012/ Kit 01
So you need to frst take month and year from above words
After then make date using get month and year , Check with financial year start and end date
If Data exist then increment no 1
Other wise reset counter
Make your logic as following way
DECLARE @intCOunt as INT = 0
select @intCOunt = COUNT(*)
from Words
where CAST ( SUBSTRING(Word,6,2)+ '/01/'+ SUBSTRING(Word,9,4) as DateTime)
BETWEEN
CAST ('04/01/'+ CAST(YEAR(GETDATE()) as varchar) as DateTime)
AND
CAST ('03/31/'+ CAST((YEAR(GETDATE()) + 1) as varchar) as DateTime)
IF (@intCOunt = 0)
BEGIN
INSERT INTO Words(Word) values ('DSCL-'+ CONVERT(char(2), GetDate(), 101) + '-' + cast(year(getdate()) as varchar) + '/ Kit ' +'01')
END
ELSE
BEGIN
INSERT INTO Words(Word) values ('DSCL-'+ CONVERT(char(2), GetDate(), 101) + '-' + cast(year(getdate()) as varchar) + '/ Kit ' + CASE WHEN LEN(@intCOunt + 1) = 1 THEN '0'+CAST((@intCOunt + 1) as varchar) ELSE CAST((@intCOunt + 1) as varchar) END )
END
Rahul BhattPosted Jul 6, 2012, 2:25 AM
you need to make one function in asp.net C# page to retrive Purchase Number
After then this purchase number direct store into DB table
If value gettting then get maximum value and find number field and auto increment value
int numbervalue =Convert.ToInt32(result.Substring(result.IndexOf("Kit") + 3, result.Length - (result.IndexOf("Kit") + 3)));
numbervalue = numbervalue + 1;
strPurchaseNumber = result.Substring(0, result.IndexOf("Kit") + 3) + " " + numbervalue.ToString("00");
Other wise insert new value
strPurchaseNumber = "DSCL-" + DateTime.Now.Month.ToString("00") +"-" + DateTime.Now.Year.ToString() + "/ Kit 01";
Full Logic
public string GetPurchaseOrderNumber()
{
string strPurchaseNumber="";
string strConnection = ConfigurationManager.ConnectionStrings["MyDbConn"].ConnectionString;
string strSelect = @"select ISNULL(MAX(Word), '')
from Words
where CAST ( SUBSTRING(Word,6,2)+ '/01/'+ SUBSTRING(Word,9,4) as DateTime)
BETWEEN
CAST ('04/01/'+ CAST(YEAR(GETDATE()) as varchar) as DateTime)
AND
CAST ('03/31/'+ CAST((YEAR(GETDATE()) + 1) as varchar) as DateTime) ";
SqlConnection con = new SqlConnection(strConnection);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSelect;
con.Open();
string result = (string)cmd.ExecuteScalar();
con.Close();
if (result != "")
{
int numbervalue = Convert.ToInt32(result.Substring(result.IndexOf("Kit") + 3, result.Length - (result.IndexOf("Kit") + 3)));
numbervalue = numbervalue + 1;
strPurchaseNumber = result.Substring(0, result.IndexOf("Kit") + 3) + " " + numbervalue.ToString("00");
}
else
{
strPurchaseNumber = "DSCL-" + DateTime.Now.Month.ToString("00") + "-" + DateTime.Now.Year.ToString() + "/ Kit 01";
}
return strPurchaseNumber;
}
Aamir KhanPosted Jul 5, 2012, 2:18 PM