public List <Csbk1> Get_csbk1_SpsList(Csbk request)
{
List <Csbk1> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
int count = 0;
if (!string.IsNullOrEmpty(request.RecordCount))
{
count = int.Parse(request.RecordCount);
}
string strWhere = "";
if (!string.IsNullOrEmpty(request.BookingNo))
{
strWhere = "Where BookingNo='" + request.BookingNo + "'";
}
var strSQL = "SELECT t1.BookingNo,JobNo,CustomerCode,CustomerName,CustomerRefNo,DeliveryEndDateTime,TotalPcs,Toaddress1,Toaddress2,Toaddress3,Toaddress4,UOMCode" +
" FROM csbk1 t1," +
"(SELECT TOP " + (count + 20) + "row_number() OVER(ORDER BY bookingNo ASC) n, bookingNo FROM csbk1 " + strWhere + " ) t2 " +
"WHERE t1.bookingNo = t2.bookingNo AND StatusCode<> 'DEL' AND t2.n >" + count +
"ORDER BY t2.n ASC";
Result = db.Select <Csbk1>(strSQL);
}
}
catch { throw; }
return(Result);
}
public int confirm_csbk1(Csbk request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
Result = db.Update <Csbk1>(
new
{
CompletedFlag = "Y"
},
p => p.BookingNo == request.BookingNo
);
}
}
catch { throw; }
return(Result);
}
public int update_csbk1(Csbk request)
{
int Result = -1;
int TrxNo = 0;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
var strSQL = "select TrxNo from csbk1 where bookingno ='" + request.BookingNo + "' ";
TrxNo = db.Scalar <int>(strSQL);
Result = db.Update <Csbk1>(
new
{
CompletedFlag = "Y",
ActualDeliveryDate = request.ActualDeliveryDate,
ActualCollectionDate = request.ActualCollectionDate,
CollectedAmt = Convert.ToDecimal(request.Amount)
},
p => p.BookingNo == request.BookingNo
);
// if (Result > 0)
// {
// Result = db.Update<Csbk2>(
// new
// {
// // CompletedFlag = request.CompletedFlag,
// CollectedPcs = request.Package
// },
// p => p.TrxNo == TrxNo
//);
// }
}
}
catch { throw; }
return(Result);
}
public int Update_Csbk2(Csbk request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
Result = db.Update <Csbk2>(
new {
CollectedPcs = int.Parse(request.CollectedPcs),
AddQty = int.Parse(request.AddQty)
},
p => p.TrxNo == request.TrxNo && p.LineItemNo == request.LineItemNo
);
}
}
catch { throw; }
return(Result);
}
public Csbk1_with_Csbk2 Get_Csbk2_List(Csbk request)
{
Csbk1_with_Csbk2 Result = new Csbk1_with_Csbk2();
Result.csbk1 = new Csbk1();
Result.csbk2s = new List <Csbk2>();
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
if (!string.IsNullOrEmpty(request.BookingNo))
{
var strSQL = "select Csbk1.BookingNo, Csbk1.JobNo,Csbk1.TrxNo,Csbk1.StatusCode as StatusCode,Csbk1.ItemNo,Csbk1.DepositAmt,Csbk1.DiscountAmt ,Csbk1.CollectedAmt,csbk1.PaidAmt from Csbk1 where BookingNo ='" + request.BookingNo + "'";
Result.csbk1 = db.Select <Csbk1>(strSQL)[0];
strSQL = " select Csbk2.TrxNo,Csbk2.LineItemNo,Csbk2.Pcs,Csbk2.UnitRate,Csbk2.CollectedPcs ,isnull(rcbx1.Description,'') as BoxCode,Csbk2.AddQty from Csbk2 left join Csbk1 on Csbk2.trxno = Csbk1.trxno left join rcbx1 on csbk2.BoxCode=rcbx1.BoxCode where BookingNo='" + request.BookingNo + "'";
Result.csbk2s = db.Select <Csbk2>(strSQL);
}
}
}
catch { throw; }
return(Result);
}
public int Update_Csbk1Collected(Csbk request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
foreach (Csbk1 p1 in request.csbk1s)
{
db.Update <Csbk1>(
new
{
CollectedAmt = p1.CollectedAmt
},
p => p.BookingNo == p1.BookingNo
);
}
Result = 1;
}
}
catch { throw; }
return(Result);
}
public List <Csbk1> Get_csbk1_List(Csbk request)
{
List <Csbk1> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
int count = 0;
if (!string.IsNullOrEmpty(request.RecordCount))
{
count = int.Parse(request.RecordCount);
}
string strWhere = "";
if (!string.IsNullOrEmpty(request.BookingNo))
{
//var strSQL = "select Csbk1.TrxNo,Csbk1.BookingNo,Csbk1.StatusCode,isnull(Csbk1.JobNo, '') as JobNo,isnull(Csbk1.BookingCustomerCode, '') as BookingCustomerCode,isnull(Csbk1.CollectionTimeStart, '') as CollectionTimeStart,isnull(Csbk1.CollectionTimeEnd, '') as CollectionTimeEnd ,sum(Csbk2.Pcs) as Pcs,isnull(rcbp1.BusinessPartyCode, '') as BusinessPartyCode,isnull(Rcbp1.PostalCode, '') as PostalCode,isnull(Rcbp1.BusinessPartyName, '') as BusinessPartyName,isnull(Rcbp1.Address1, '') as Address1,isnull(Rcbp1.Address2, '') as Address2,isnull(Rcbp1.Address3, '') as Address3,isnull(Rcbp1.Address4, '') as Address4 ,isnull(Csbk1.CompletedFlag,'') AS CompletedFlag" +
// " from Csbk1 left join Csbk2 on Csbk1.TrxNo = Csbk2.TrxNo left" +
// " join rcbp1 on Csbk1.BookingCustomerCode = rcbp1.BusinessPartyCode " + strWhere +
// " group by Csbk1.jobno,rcbp1.BusinessPartyCode,Csbk1.BookingNo,Csbk1.StatusCode,Rcbp1.PostalCode,Rcbp1.BusinessPartyName,Rcbp1.Address1,Rcbp1.Address2,Rcbp1.Address3,Rcbp1.Address4,Csbk1.BookingCustomerCode,Csbk1.CollectionTimeStart,Csbk1.CollectionTimeEnd,Csbk1.CompletedFlag,Csbk1.TrxNo";
strWhere = "Where BookingNo like'%" + request.BookingNo + "' and isnull(Csbk1.CompletedFlag,'')<>'Y'";
var strSQL = "select Csbk1.TrxNo," +
"isnull((Select top 1 TimeFrom From Todr2 Where District like '%' + Rcbp1.DistrictCode + '%' and day in ( case when DatePart(W, GETDATE()) = 1 then 'SUN'" +
" when DatePart(W, GETDATE()) = 2 then 'MON'" +
" when DatePart(W, GETDATE()) = 3 then 'TUE'" +
" when DatePart(W, GETDATE()) = 4 then 'WED'" +
" when DatePart(W, GETDATE()) = 5 then 'THU'" +
" when DatePart(W, GETDATE()) = 6 then 'FRI'" +
" when DatePart(W, GETDATE()) = 7 then 'SAT'" +
" end)),'') AS TimeFrom," +
"isnull((Select top 1 TimeTo From Todr2 Where District like '%' + Rcbp1.DistrictCode + '%' and day in ( case when DatePart(W, GETDATE()) = 1 then 'SUN'" +
" when DatePart(W, GETDATE()) = 2 then 'MON'" +
" when DatePart(W, GETDATE()) = 3 then 'TUE'" +
" when DatePart(W, GETDATE()) = 4 then 'WED'" +
" when DatePart(W, GETDATE()) = 5 then 'THU'" +
" when DatePart(W, GETDATE()) = 6 then 'FRI'" +
" when DatePart(W, GETDATE()) = 7 then 'SAT'" +
" end )),'') AS TimeTo," +
" isnull((Select top 1 TimeFrom From Todr2 Where District like '%' + Rcbp1.DistrictCode + '%' and day in ( case when DatePart(W, GETDATE()) = 1 then 'SUN'" +
" when DatePart(W, GETDATE()) = 2 then 'MON'" +
" when DatePart(W, GETDATE()) = 3 then 'TUE'" +
" when DatePart(W, GETDATE()) = 4 then 'WED'" +
" when DatePart(W, GETDATE()) = 5 then 'THU'" +
" when DatePart(W, GETDATE()) = 6 then 'FRI'" +
" when DatePart(W, GETDATE()) = 7 then 'SAT'" +
" end)),'') AS ColTimeFrom," +
" isnull((Select top 1 TimeTo From Todr2 Where District like '%' + Rcbp1.DistrictCode + '%' and day in ( case when DatePart(W, GETDATE()) = 1 then 'SUN'" +
" when DatePart(W, GETDATE()) = 2 then 'MON'" +
" when DatePart(W, GETDATE()) = 3 then 'TUE'" +
" when DatePart(W, GETDATE()) = 4 then 'WED'" +
" when DatePart(W, GETDATE()) = 5 then 'THU'" +
" when DatePart(W, GETDATE()) = 6 then 'FRI'" +
" when DatePart(W, GETDATE()) = 7 then 'SAT'" +
" end )),'') AS ColTimeTo," +
" Csbk1.BookingNo,Csbk1.StatusCode,isnull(Csbk1.JobNo, '') as JobNo,isnull(Csbk1.BookingCustomerCode, '') as BookingCustomerCode,isnull(Csbk1.CollectionTimeStart, '') as CollectionTimeStart,isnull(Csbk1.CollectionTimeEnd, '') as CollectionTimeEnd ,sum(Csbk2.Pcs) as Pcs, isnull(rcbp1.BusinessPartyCode,'') as BusinessPartyCode,isnull(Rcbp1.PostalCode, '') as PostalCode,isnull(rcbp1.FirstName, '')+isnull(rcbp1.LastName, '') as BusinessPartyName,isnull(Rcbp1.Address1, '') as Address1,isnull(Rcbp1.Address2, '') as Address2,isnull(Rcbp1.Address3, '') as Address3,isnull(Rcbp1.Address4, '') as Address4 ,isnull(Csbk1.CompletedFlag, '') AS CompletedFlag, (Select CONVERT(varchar(100), GETDATE(), 121)) as ScanDate " +
" from Csbk1 left join Csbk2 on Csbk1.TrxNo = Csbk2.TrxNo left" +
" join rcbp1 on Csbk1.BookingCustomerCode = rcbp1.BusinessPartyCode " + strWhere +
" group by Csbk1.jobno,rcbp1.BusinessPartyCode,Csbk1.BookingNo,Csbk1.StatusCode,Rcbp1.PostalCode,Rcbp1.BusinessPartyName,Rcbp1.Address1,Rcbp1.Address2,Rcbp1.Address3,Rcbp1.Address4,Csbk1.BookingCustomerCode,Csbk1.CollectionTimeStart,Csbk1.CollectionTimeEnd,Csbk1.CompletedFlag,Csbk1.TrxNo ,Rcbp1.DistrictCode,rcbp1.FirstName,rcbp1.LastName";
Result = db.Select <Csbk1>(strSQL);
// //=======
// " FROM csbk1 t1," +
// "(SELECT TOP " + (count + 20) + "row_number() OVER(ORDER BY bookingNo ASC) n, bookingNo FROM csbk1 " + strWhere + " ) t2 " +
// "WHERE t1.bookingNo = t2.bookingNo AND StatusCode<> 'DEL' AND t2.n >" + count +
// "ORDER BY t2.n ASC";
////===============
}
}
}
catch { throw; }
return(Result);
}
public int confirm_csbk1(Csbk 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);
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);
NextNo = CheckUpdateFieldLength(NextNo);
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),
PaidAmt = SumReceiptAmt(request.BookingNo),
CompletedFlag = "Y",
ActualCollectionDate = request.ActualCollectionDate,
CollectedAmt = Convert.ToDecimal(request.Amount)
},
p => p.BookingNo == request.BookingNo
);
}
}
}
catch { throw; }
return(Result);
}
