//public int CheckStatus(Slcr request) {
// int Result = -1;
// try
// {
// using (var db = DbConnectionFactory.OpenDbConnection())
// {
// string strSql = "";
// if ( ! string.IsNullOrEmpty(request.BookingNo))
// {
// strSql = "Select count(*) From slcr1 Where BookingNo='" + request.BookingNo + "'";
// }
// Result = db.Scalar<int>(strSql);
// }
// }
// catch { throw; }
// return Result;
//}
public List <Slcr1> Get_slcr1(Slcr request)
{
List <Slcr1> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
string strWhere = "";
if (!string.IsNullOrEmpty(request.BookingNo))
{
strWhere = "Where BookingNo='" + request.BookingNo + "'";
}
var strSQL = "select sum(ReceiptAmt) as ReceiptAmt,BookingNo from slcr1 " + strWhere + "group by BookingNo";
Result = db.Select <Slcr1>(strSQL);
}
}
catch { throw; }
return(Result);
}
public int Complete_Slcr1(Slcr request)
{
int Result = -1;
string NextNo = "";
int intMonth = 0;
request.ChequeAmt = 0;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
var strSQL = "SELECT " +
" case " +
" when MONTH(GETDATE()) = '1' then(select Mth01NextNo from sanm2 where TrxNo = (Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate())))" +
" when MONTH(GETDATE()) = '2' then(select Mth02NextNo from sanm2 where TrxNo = (Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate())))" +
" when MONTH(GETDATE()) = '3' then(select Mth03NextNo from sanm2 where TrxNo = (Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate())))" +
" when MONTH(GETDATE()) = '4' then(select Mth04NextNo from sanm2 where TrxNo = (Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate())))" +
" when MONTH(GETDATE()) = '5' then(select Mth05NextNo from sanm2 where TrxNo = (Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate())))" +
" when MONTH(GETDATE()) = '6' then(select Mth06NextNo from sanm2 where TrxNo = (Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate())))" +
" when MONTH(GETDATE()) = '7' then(select Mth07NextNo from sanm2 where TrxNo = (Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate())))" +
" when MONTH(GETDATE()) = '8' then(select Mth08NextNo from sanm2 where TrxNo = (Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate())))" +
" when MONTH(GETDATE()) = '9' then(select Mth09NextNo from sanm2 where TrxNo = (Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate())))" +
" when MONTH(GETDATE()) = '10' then(select Mth10NextNo from sanm2 where TrxNo = (Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate())))" +
" when MONTH(GETDATE()) = '11' then(select Mth11NextNo from sanm2 where TrxNo = (Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate())))" +
" when MONTH(GETDATE()) = '12' then(select Mth12NextNo from sanm2 where TrxNo = (Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate())))" +
"end as 'NextNo' ";
NextNo = db.Scalar <string>(strSQL);
NextNo = CheckUpdateFieldLength(NextNo);
if (NextNo.Length > 0)
{
db.Insert(
new Slcr1
{
ReceiptNo = "RE" + DateTime.Now.ToString("yyMMdd") + NextNo,
JobNo = request.JobNo,
BookingNo = request.BookingNo,
BookingLine = null,
Remark = null,
ReceiptType = "Payment",
CashAmt = request.CashAmt,
ChequeAmt = request.ChequeAmt,
ReceiptAmt = request.CashAmt + request.ChequeAmt,
ReceiptDate = DateTime.Now,
ChequeDate = null,
UpdateBy = request.UpdateBy,
CollectBy = request.CollectBy,
}
);
Result = 0;
strSQL = "SELECT " +
" case " +
" when MONTH(GETDATE()) = '1' then 1" +
" when MONTH(GETDATE()) = '2' then 2 " +
" when MONTH(GETDATE()) = '3' then 3 " +
" when MONTH(GETDATE()) = '4' then 4 " +
" when MONTH(GETDATE()) = '5' then 5 " +
" when MONTH(GETDATE()) = '6' then 6 " +
" when MONTH(GETDATE()) = '7' then 7 " +
" when MONTH(GETDATE()) = '8' then 8 " +
" when MONTH(GETDATE()) = '9' then 9 " +
" when MONTH(GETDATE()) = '10' then 10 " +
" when MONTH(GETDATE()) = '11' then 11 " +
" when MONTH(GETDATE()) = '12' then 12" +
" end as 'NextNo' ";
intMonth = db.Scalar <int>(strSQL);
if (intMonth == 1)
{
db.Update("sanm2", " Mth01NextNo = '" + NextNo + "'", "trxno =(Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate()))");
}
else if (intMonth == 2)
{
db.Update("sanm2", " Mth02NextNo = '" + NextNo + "'", "trxno =(Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate()))");
}
else if (intMonth == 3)
{
db.Update("sanm2", " Mth03NextNo = '" + NextNo + "'", "trxno =(Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate()))");
}
else if (intMonth == 4)
{
db.Update("sanm2", " Mth04NextNo = '" + NextNo + "'", "trxno =(Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate()))");
}
else if (intMonth == 5)
{
db.Update("sanm2", " Mth05NextNo = '" + NextNo + "'", "trxno =(Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate()))");
}
if (intMonth == 6)
{
db.Update("sanm2", " Mth06NextNo = '" + NextNo + "'", "trxno =(Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate()))");
}
else if (intMonth == 7)
{
db.Update("sanm2", " Mth07NextNo = '" + NextNo + "'", "trxno =(Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate()))");
}
else if (intMonth == 8)
{
db.Update("sanm2", " Mth08NextNo = '" + NextNo + "'", "trxno =(Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate()))");
}
if (intMonth == 9)
{
db.Update("sanm2", " Mth09NextNo = '" + NextNo + "'", "trxno =(Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate()))");
}
else if (intMonth == 10)
{
db.Update("sanm2", " Mth10NextNo = '" + NextNo + "'", "trxno =(Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate()))");
}
else if (intMonth == 11)
{
db.Update("sanm2", " Mth11NextNo = '" + NextNo + "'", "trxno =(Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate()))");
}
else if (intMonth == 12)
{
db.Update("sanm2", " Mth12NextNo = '" + NextNo + "'", "trxno =(Select TrxNo From Sanm1 Where NumberType = 'Slcr') and YEAR = (select year(getdate()))");
}
Result = db.Update <Csbk1>(
new
{
DepositAmt = Convert.ToDecimal(0),
PaidAmt = Convert.ToDecimal(request.CashAmt + request.ChequeAmt)
},
p => p.BookingNo == request.BookingNo
);
}
}
}
catch { throw; }
return(Result);
}