1 string strSql = "SELECT * from vSelShipModeAUGReporting Where 1 = 1 " ;
2 string strWhere = string.Empty;
3
4 if ( strShipMode.Length > 0 )
5 {
6 if ( strShipMode.Trim().ToUpper() == Constants.FCL.Trim())
7 {
8 strWhere += " AND RTRIM([ShipMode]) = " + "'" + strShipMode.Trim().ToUpper() + "'";
9 }
10 else
11 {
12 strWhere += " AND ISNULL(IsNew, 0) = 0 ";
13 strWhere += " AND RTRIM([ShipMode]) <> " + "'" + Constants.FCL.Trim() + "'";
14 }
15 }
16 if(strPickupFrom.Trim().Length > 0)
17 {
18 strWhere += " AND CONVERT(VARCHAR(12),PickupDate,110) >= CONVERT(VARCHAR(12),'" + strPickupFrom.Trim() + "',110) ";
19 }
20 if(strPickupTo.Trim().Length > 0)
21 {
22 strWhere += " AND CONVERT(VARCHAR(12),PickupDate,110) <= CONVERT(VARCHAR(12),'" + strPickupTo.Trim() + "',110) ";
23 }
24 strSql += strWhere + " ORDER BY AppointID ASC " ;
25
26 DataSet _result = new DataSet();
27 SQLHelper.FillDataSet(strSql,_result,"tb_AUGReport");
28
29 // For same appointment, if container no is same, then use average value. (FCL)
30 if ( strShipMode.Trim().ToUpper() == Constants.FCL.Trim() )
31 {
32 string strSqlFCL = "SELECT APPOINTID,CONTAINERNO,COUNT(CONTAINERNO) AS COUNTNUM FROM vSelShipModeAUGReporting WHERE 1 = 1 ";
33 strSqlFCL += strWhere + " GROUP BY APPOINTID,CONTAINERNO ";
34
35 System.Data.DataTable dtCalculateSumContainerNO = new DataTable();
36 dtCalculateSumContainerNO = SQLHelper.getSelectData(strSqlFCL).Tables[0].Copy();
37 dtCalculateSumContainerNO.TableName = "dtCalculateSumContainerNO";
38 _result.Tables.Add(dtCalculateSumContainerNO);
39 }
40 // calculate trucking fee by LCL OR FCL
41 System.Data.DataTable dtCalculateSumTruckFee = new DataTable();
42 DateTime dtReceiveFrom = Utility.StringUtil.getMinDateIfEmpty(strPickupFrom);
43 DateTime dtReceiveTo = Utility.StringUtil.getMinDateIfEmpty(strPickupTo);
44 SqlParameter[] objParameters = {
45 new SqlParameter("@StartDate",SqlDbType.SmallDateTime),
46 new SqlParameter("@EndDate",SqlDbType.SmallDateTime),
47 new SqlParameter("@ShippingMode",SqlDbType.VarChar,5)
48 };
49
50 if (dtReceiveFrom!=DateTime.MinValue )
51 objParameters[0].Value = dtReceiveFrom;
52 if (dtReceiveTo!=DateTime.MinValue )
53 objParameters[1].Value = dtReceiveTo;
54 objParameters[2].Value = strShipMode.Trim();
55 dtCalculateSumTruckFee = SQLHelper.dtRunProdure("CCC_P_SelShipModeAUGReporting",objParameters,"tb_CalculateSumTruckFee").Tables[0].Copy();
56
57 System.Data.DataTable dtCalculateSumCONSOTruckFee = new DataTable();
58
59 if ( strShipMode.Trim().ToUpper() == Constants.LCL.Trim() )
60 {
61 // calculate trucking fee by CONSO
62 SqlParameter[] objParameters2 = {
63 new SqlParameter("@StartDate",SqlDbType.SmallDateTime),
64 new SqlParameter("@EndDate",SqlDbType.SmallDateTime),
65 new SqlParameter("@ShippingMode",SqlDbType.VarChar,5)
66 };
67 if (dtReceiveFrom!=DateTime.MinValue )
68 objParameters2[0].Value = dtReceiveFrom;
69 if (dtReceiveTo!=DateTime.MinValue )
70 objParameters2[1].Value = dtReceiveTo;
71 objParameters2[2].Value = Constants.CONSO.Trim();
72 dtCalculateSumCONSOTruckFee = SQLHelper.dtRunProdure("CCC_P_SelShipModeAUGReporting",objParameters2,"tb_CalculateSumCONSOTruckFee").Tables[0];
73 }
74
75 if ( dtCalculateSumCONSOTruckFee.Rows.Count > 0)
76 {
77 foreach ( DataRow dr in dtCalculateSumCONSOTruckFee.Rows)
78 {
79 dtCalculateSumTruckFee.Rows.Add(dr.ItemArray);
80 }
81 }
82
83 _result.Tables.Add(dtCalculateSumTruckFee);
84
85 // _result = SQLHelper.getSelectData(strSql);
86 return _result;
View Code
