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;