jquerydate: 2012

Sunday, November 25, 2012

How to Install Cakephp in IIS 7.5

During my last project, I used cakephp as my php framework. One of the objective of using cakephp is that you can developed a system on the fly. It has great documentation. unfortunately, when it comes to installing cakephp running on IIS it lacks some documentation. The cookbook is quite frank about needing to do some tweaks to let cakephp run under IIS environment. Fortunately, after a few googles and research, I was able to let the cakephp run in IIS. Heres how.

Prerequisites:

Need to install url rewrite. You can download the url rewrite here
Fresh cakephp

Testing and Installation:

Download and unpacked cakephp in c:\inetpub\wwwroot\
In IIS console under url rewrite, import the .httaccess in cakephp, webroot, and in app folder.
Once imported, IIS will create webconfig file. The webconfig file resides in root directory folder of cakephp. Modified it to show show something like below

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <system.webServer>
        <rewrite>
            <rules>
                <clear/>
                 

                 <rule name="Imported Rule 0" stopProcessing="true">
                   <match url="^(img|css|files|js)(.*)$"></match>
                   <action type="Rewrite" url="/cakephp/app/webroot/{R:1}{R:2}" appendQueryString="false"></action>
                 </rule>

                 <rule name="Imported Rule 1" stopProcessing="true">
                   <match url="^(.*)$" ignoreCase="false" />
                   <conditions logicalGrouping="MatchAll">
                     <add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="true" />
                     <add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true" />
                   </conditions>
                   <action type="Rewrite" url="index.php?url={R:1}" appendQueryString="true" />
                 </rule>

                 <rule name="Imported Rule 2" stopProcessing="true">
                   <match url="^$" ignoreCase="false" />
                   <action type="Rewrite" url="/cakephp/app/webroot/" />
                 </rule>

                 <rule name="Imported Rule 3" stopProcessing="true">
                   <match url="(.*)" ignoreCase="false" />
                   <action type="Rewrite" url="/cakephp/app/webroot/{R:1}" />
                 </rule>

                 <rule name="Imported Rule 4" stopProcessing="true">
                   <match url="^(.*)$" ignoreCase="false" />
                   <conditions logicalGrouping="MatchAll">
                     <add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="true" />
                     <add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true" />
                   </conditions>
                   <action type="Rewrite" url="index.php?url={R:1}" appendQueryString="true" />
                 </rule>

            </rules>
        </rewrite>
    </system.webServer>
</configuration>
Your cakephp will now work in IIS.

Happy coding

Monday, August 20, 2012

Targeting Tribal Leaders: A New Militant Tactic in Sinai

"Targeting Tribal Leaders: A New Militant Tactic in Sinai is republished with permission of Stratfor."

Militants killed Egyptian tribal leader Khalaf al-Menahy and his son Aug. 13 as the two were returning from a conference in east Sinai organized and attended by tribal leaders to denounce militancy, according to Sinai security forces. The senior al-Menahy was a prominent proponent of bolstering the Sinai Peninsula's representation in Egypt's parliament and of improving security in the region. He also was a prominent sheikh in the Sawarka tribe, said to be the largest in Sinai. Following his burial Aug. 13, the tribe vowed to seek vengeance.

This is the first reported case of militants attacking tribal leaders in Sinai. It comes soon after an attack on Egyptian security forces Aug. 5 and an attack on military checkpoints in northern Sinai on Aug. 8.

Although the militant tactic of targeting tribal leaders is new to Sinai, the tactic has been common in conflict zones in the Middle East and South Asia, such as in Yemen, Iraq and the Afghanistan-Pakistan border region. Though it can offer many benefits to these militants -- including weakening the targeted tribe and possibly leading to its co-option -- these kinds of attacks tend to only succeed in zones with little government control and against tribes that cannot effectively retaliate. Examining similar instances of this tactic thus provides a helpful tool for assessing the consequences of attacks against tribal elements in the Sinai Peninsula.

A Widespread Militant Tactic

Yemen

Al Qaeda in the Arabian Peninsula has operated openly in Yemen's tribal-dominated southern and eastern provinces for years. It has sought to expand its presence and operations by winning over local tribes using tactics such as strategic marriages.

Lately, it appears to have begun a shift from wooing tribal leaders to intimidating them. Al Qaeda in the Arabian Peninsula recently failed in an attempt to assassinate tribal leader Majed al-Dhahab in the city of Radda in Bayda province. An important tribal leader, al-Dhahab participated in the offensive to drive al Qaeda -- and his own cousin, a local al Qaeda leader -- from the region after the militant group seized control of Radda in January. Al-Dhahab's son received a package that unbeknownst to him contained a bomb, which he was instructed to give to his father. However, the package exploded in his arms Aug. 4 before he could deliver it. Immediately after his son's death, al-Dhahab received a call warning him that the group would kill anyone who opposed it.

The group followed up with another attack on tribal elements Aug. 5. A suicide bomber detonated an explosive device at a wake in Jaar, killing 45 people. The dead included several tribal fighters who had participated in the June Yemeni government offensive against al Qaeda in the Arabian Peninsula, and the wounded included a tribal leader.

The region's tribes have not publicly vowed to retaliate against the militant group. If they are capable of doing so, they probably will respond to al Qaeda in the Arabian Peninsula's attacks. But the tribes could be too weak to mount an effective response, especially in the wake of attacks on their leadership structure. This could cause some tribesmen to abandon the fight, allowing militants to try to resume activity in the region's towns should they wish.

Iraq and the Afghanistan-Pakistan Border

Although new to Yemen, militants frequently used the tactic of attacking tribal leaders during the U.S. involvement in Iraq and Afghanistan. The tactic is still frequently used, especially in the Afghanistan-Pakistan border region. In one significant instance, in 2007 al Qaeda in Iraq assassinated high-profile Sunni tribal Sheikh Abdul-Sattar Abu Risha, who led the Anbar Awakening Council. A U.S. ally, Abu Risha had formed the council, uniting dozens of Sunni tribes in the province against al Qaeda in Iraq. His killing backfired on the militant group, generating a massive outpouring of sympathy for Abu Risha and prompting the tribes in the province to join in vowing to fight al Qaeda in Iraq to the death.

In southern Afghanistan and northwestern Pakistan, the Afghan Taliban are deeply embedded into the tribal system. They have effectively used the tactic of assassinating tribal leaders to eliminate obstacles to their operations and evolution. To this end, they regularly employ suicide operations, armed assaults and roadside bombs against anti-Taliban militias known as lashkars and against tribal leaders in northwestern Pakistan.

One area particularly affected by such attacks is Bajaur, a Pakistani agency that borders Afghanistan's Kunar province. After numerous attacks on tribal leaders and members of peace committees in Bajaur, the Mamond tribe announced July 25 that the tribal leaders had formed a lashkar to prevent cross-border attacks. Hundreds of elders, leaders and religious figures of various subtribes and peace committees pledged their support for this militia. As with the killing of Abu Risha, the Afghan Taliban attacks on tribesmen and leadership in the region spurred a fiercely united response across numerous tribes, with the new militia even expressing a willingness to enter Afghanistan to attack Taliban leaders.

Upsides and Downsides of a Militant Tactic

Militant groups attack tribal leaders to increase their influence and area of operations. From the militants' perspective, removing a tribal leader ideally will weaken the targeted tribe. This could end the tribes' resistance and even lead to the its being co-opted by the militant group due to a leadership vacuum following the militant attack. The weakening of the tribe could leave the group no choice but to allow the militant group to operate unchallenged in its territory. Even though assassinated tribal leaders are replaced and the leadership structure remains intact, tribal leaders in the area could be persuaded to adopt a more accommodating stance on the presence of militants.

Success for a militant group in the long term happens under two conditions. First, the militants must be acting in an area with a tribal patronage network and limited government oversight. Without such a network, attacks on tribal leaders in efforts to co-opt and intimidate that tribe would not provide any significant gain. In Yemen, for example, the patronage and tribal network are very strong and in most cases enjoy greater legitimacy and power than the government. Attacks against tribal chiefs there are accordingly tantamount to attacks on the local government. On the one hand, that means tribal networks can band together and shun foreign militant elements as one community. On the other hand, if al Qaeda in the Arabian Peninsula is able to coerce a tribe into aligning with it, the militant group will then enjoy access to that tribes' resources, will gain the ability to plan and launch attacks in that area, and could even gain better relations with neighboring tribes.

Second, the group must be militarily capable of overwhelming the targeted tribe and its allies or at least of gaining the upper hand. As can be seen from the Iraq example, killing Abu Risha backfired because his tribe was large, committed and militarily strong, and it had the support of several allied Sunni tribes that belonged to his Anbar Awakening Council.

The tactic of targeting a tribal leader thus comes with certain risks. When the aforementioned two conditions are not met, a militant group exposes itself to great danger when it targets tribal leaders.

Consequences of the Sinai Assassination

The Sinai Peninsula meets the requirement of limited government control and strong tribal networks. The question then becomes whether the Sinai tribes can muster a strong defense against the militants. In the coming weeks, it will be important to look for signs of the retaliation pledged by al-Menahy's Sawarka tribe and others allied with it. This retaliation could come in the form of attacks against the militants passing through Sawarka and its allied tribes' territory.

Tribal retaliation could also come in the less aggressive, yet still effective, form of supplying increased logistical support and intelligence to the Egyptian government. Increased weapons seizures and the arrest of key leaders suggest that tribal sources on the ground are providing intelligence to Cairo. A targeted campaign against the militants already has begun, with Egyptian planes bombing the mountains of El Arish on Aug. 15. The intelligence for these attacks likely came from local tribes.

The success of tribal and Egyptian security efforts against the militants will determine whether the militants miscalculated their position in Sinai when they attacked a key tribal leader. The resilience of militants in Sinai also will help determine whether they can continue to stage attacks against Egypt and Israel.

The Israeli Crisis

"The Israeli Crisis is republished with permission of Stratfor."

Crises are normally short, sharp and intense affairs. Israel's predicament has developed on a different time frame, is more diffuse than most crises and has not reached a decisive and intense moment. But it is still a crisis. It is not a crisis solely about Iran, although the Israeli government focuses on that issue. Rather, it is over Israel's strategic reality since 1978, when it signed the Camp David accords with Egypt.
Perhaps the deepest aspect of the crisis is that Israel has no internal consensus on whether it is in fact a crisis, or if so, what the crisis is about. The Israeli government speaks of an existential threat from Iranian nuclear weapons. I would argue that the existential threat is broader and deeper, part of it very new, and part of it embedded in the founding of Israel.
Israel now finds itself in a long-term crisis in which it is struggling to develop a strategy and foreign policy to deal with a new reality. This is causing substantial internal stress, since the domestic consensus on Israeli policy is fragmenting at the same time that the strategic reality is shifting. Though this happens periodically to nations, Israel sees itself in a weak position in the long run due to its size and population, despite its current military superiority. More precisely, it sees the evolution of events over time potentially undermining that military reality, and it therefore feels pressured to act to preserve it. How to preserve its superiority in the context of the emerging strategic reality is the core of the Israeli crisis.

Egypt

Since 1978, Israel's strategic reality had been that it faced no threat of a full peripheral war. After Camp David, the buffer of the Sinai Peninsula separated Egypt and Israel, and Egypt had a government that did not want that arrangement to break. Israel still faced a formally hostile Syria. Syria had invaded Lebanon in 1976 to crush the Palestine Liberation Organization based there and reconsolidate its hold over Lebanon, but knew it could not attack Israel by itself. Syria remained content reaching informal understandings with Israel. Meanwhile, relatively weak and isolated Jordan depended on Israel for its national security. Lebanon alone was unstable. Israel periodically intervened there, not very successfully, but not at very high cost.
The most important of Israel's neighbors, Egypt, is now moving on an uncertain course. This weekend, new Egyptian President Mohammed Morsi removed five key leaders of the military and the Supreme Council of the Armed Forces and revoked constitutional amendments introduced by the military. There are two theories on what has happened. In the first, Morsi -- who until his election was a senior leader of the country's mainstream Islamist movement, the Muslim Brotherhood -- is actually much more powerful than the military and is acting decisively to transform the Egyptian political system. In the second, this is all part of an agreement between the military and the Muslim Brotherhood that gives Morsi the appearance of greater power while actually leaving power with the military.
On the whole, I tend to think that the second is the case. Still, it is not clear how this will evolve: The appearance of power can turn into the reality of power. Despite any sub rosa agreements between the military and Morsi, how these might play out in a year or two as the public increasingly perceives Morsi as being in charge -- limiting the military's options and cementing Morsi's power -- is unknown. In the same sense, Morsi has been supportive of security measures taken by the military against militant Islamists, as was seen in the past week's operations in the Sinai Peninsula.
The Sinai remains a buffer zone against major military forces but not against the paramilitaries linked to radical Islamists who have increased their activities in the peninsula since the fall of former President Hosni Mubarak in February 2011. Last week, they attacked an Egyptian military post on the Gaza border, killing 16 Egyptian soldiers. This followed several attacks against Israeli border crossings. Morsi condemned the attack and ordered a large-scale military crackdown in the Sinai. Two problems could arise from this.
First, the Egyptians' ability to defeat the militant Islamists depends on redefining the Camp David accords, at least informally, to allow Egypt to deploy substantial forces there (though even this might not suffice). These additional military forces might not threaten Israel immediately, but setting a precedent for a greater Egyptian military presence in the Sinai Peninsula could eventually lead to a threat.
This would be particularly true if Morsi and the Muslim Brotherhood impose their will on the Egyptian military. If we take Morsi at face value as a moderate, the question becomes who will succeed him. The Muslim Brotherhood is clearly ascendant, and the possibility that a secular democracy would emerge from the Egyptian uprising is unlikely. It is also clear that the Muslim Brotherhood is a movement with many competing factions. And it is clear from the elections that the Muslim Brotherhood represents the most popular movement in Egypt and that no one can predict how it will evolve or which factions will dominate and what new tendencies will arise. Egypt in the coming years will not resemble Egypt of the past generation, and that means that the Israeli calculus for what will happen on its southern front will need to take Hamas in Gaza into account and perhaps an Islamist Egypt prepared to ally with Hamas.

Syria and Lebanon

A similar situation exists in Syria. The secular and militarist regime of the al Assad family is in serious trouble. As mentioned, the Israelis had a working relationship with the Syrians going back to the Syrian invasion of Lebanon against the Palestine Liberation Organization in 1976. It was not a warm relationship, but it was predictable, particularly in the 1990s: Israel allowed Syria a free hand in Lebanon in exchange for Damascus' limiting Hezbollah's actions.
Lebanon was not exactly stable, but its instability hewed to a predictable framework. That understanding broke down when the United States seized an opportunity to force Syria to retreat from Lebanon in 2006 following the 2005 assassination of Lebanese Prime Minister Rafik al-Hariri. The United States used the Cedar Revolution that rose up in defiance of Damascus to retaliate against Syria for allowing al Qaeda to send jihadists into Iraq from Syria.
This didn't spark the current unrest in Syria, which appears to involve a loose coalition of Sunnis, including elements of the Muslim Brotherhood and other Islamists. Though Israel far preferred Syrian President Bashar al Assad to them, al Assad himself was shifting his behavior. The more pressure he came under, the more he became dependent on Iran. Israel began facing the unpleasant prospect of a Sunni Islamist government emerging or a government heavily dependent on Iran. Neither outcome appealed to Israel, and neither outcome was in Israel's control.
Just as dangerous to Israel would be the Lebanonization of Syria. Syria and Lebanon are linked in many ways, though Lebanon's political order was completely different and Syria could serve as a stabilizing force for it. There is now a reasonable probability that Syria will become like Lebanon, namely, a highly fragmented country divided along religious and ethnic lines at war with itself. Israel's best outcome would be for the West to succeed in preserving Syria's secular military regime without al Assad. But it is unclear how long a Western-backed regime resting on the structure of al Assad's Syria would survive. Even the best outcome has its own danger. And while Lebanon itself has been reasonably stable in recent years, when Syria catches a cold, Lebanon gets pneumonia. Israel thus faces the prospect of declining security to its north.

The U.S. Role and Israel's Strategic Lockdown

It is important to take into account the American role in this, because ultimately Israel's national security -- particularly if its strategic environment deteriorates -- rests on the United States. For the United States, the current situation is a strategic triumph. Iran had been extending its power westward, through Iraq and into Syria. This represented a new force in the region that directly challenged American interests. Where Israel originally had an interest in seeing al Assad survive, the United States did not. Washington's primary interest lay in blocking Iran and keeping it from posing a threat to the Arabian Peninsula. The United States saw Syria, particularly after the uprising, as an Iranian puppet. While the United States was delighted to see Iran face a reversal in Syria, Israel was much more ambivalent about that outcome.
The Israelis are always opposed to the rising regional force. When that was Egyptian leader Gamal Abdel Nasser, they focused on Nasser. When it was al Qaeda and its sympathizers, they focused on al Qaeda. When it was Iran, they focused on Tehran. But simple opposition to a regional tendency is no longer a sufficient basis for Israeli strategy. As in Syria, Israel must potentially oppose all tendencies, where the United States can back one. That leaves Israeli policy incoherent. Lacking the power to impose a reality on Syria, the best Israel can do is play the balance of power. When its choice is between a pro-Iranian power and a Sunni Islamist power, it can no longer play the balance of power. Since it lacks the power to impose a reality, it winds up in a strategic lockdown.
Israel's ability to influence events on its borders was never great, but events taking place in bordering countries are now completely beyond its control. While Israeli policy has historically focused on the main threat, using the balance of power to stabilize the situation and ultimately on the decisive use of military force, it is no longer possible to identify the main threat. There are threats in all of its neighbors, including Jordan (where the kingdom's branch of the Muslim Brotherhood is growing in influence while the Hashemite monarchy is reviving relations with Hamas). This means using the balance of power within these countries to create secure frontiers is no longer an option. It is not clear there is a faction for Israel to support or a balance that can be achieved. Finally, the problem is political rather than military. The ability to impose a political solution is not available.
Against the backdrop, any serious negotiations with the Palestinians are impossible. First, the Palestinians are divided. Second, they are watching carefully what happens in Egypt and Syria since this might provide new political opportunities. Finally, depending on what happens in neighboring countries, any agreement Israel might reach with the Palestinians could turn into a nightmare.
The occupation therefore continues, with the Palestinians holding the initiative. Unrest begins when they want it to begin and takes the form they want it to have within the limits of their resources. The Israelis are in a responsive mode. They can't eradicate the Palestinian threat. Extensive combat in Gaza, for example, has both political consequences and military limits. Occupying Gaza is easy; pacifying Gaza is not.

Israel's Military and Domestic Political Challenges

The crisis the Israelis face is that their levers of power, the open and covert relationships they had, and their military force are not up to the task of effectively shaping their immediate environment. They have lost the strategic initiative, and the type of power they possess will not prove decisive in dealing with their strategic issues. They no longer are operating at the extremes of power, but in a complex sphere not amenable to military solutions.
Israel's strong suit is conventional military force. It can't fully understand or control the forces at work on its borders, but it can understand the Iranian nuclear threat. This leads it to focus on the sort of conventional conflict it excels at, or at least used to excel at. The 2006 war with Hezbollah was quite conventional, but Israel was not prepared for an infantry war. The Israelis instead chose to deal with Lebanon via an air campaign, but that failed to achieve their political ends.
The Israelis want to redefine the game to something they can win, which is why their attention is drawn to the Iranian nuclear program. Of all their options in the region, a strike against Iran's nuclear facilities apparently plays to their strengths. Two things make such a move attractive. The first is that eliminating Iran's nuclear capability is desirable for Israel. The nuclear threat is so devastating that no matter how realistic the threat is, removing it is desirable.
Second, it would allow Israel to demonstrate the relevance of its power in the region. It has been a while since Israel has had a significant, large-scale military victory. The 1980s invasion of Lebanon didn't end well; the 2006 war was a stalemate; and while Israel may have achieved its military goals in the 2008 invasion of Gaza, that conflict was a political setback. Israel is still taken seriously in the regional psychology, but the sense of inevitability Israel enjoyed after 1967 is tattered. A victory on the order of destroying Iranian weapons would reinforce Israel's relevance.
It is, of course, not clear that the Israelis intend to launch such an attack. And it is not clear that such an attack would succeed. It is also not clear that the Iranian counter at the Strait of Hormuz wouldn't leave Israel in a difficult political situation, and above all it is not clear that Egyptian and Syrian factions would even be impressed by the attacks enough to change their behavior.
Israel also has a domestic problem, a crisis of confidence. Many military and intelligence leaders oppose an attack on Iran. Part of their opposition is rooted in calculation. Part of it is rooted in a series of less-than-successful military operations that have shaken their confidence in the military option. They are afraid both of failure and of the irrelevance of the attack on the strategic issues confronting Israel.
Political inertia can be seen among Israeli policymakers. Prime Minister Benjamin Netanyahu tried to form a coalition with the centrist Kadima Party, but that fell apart over the parochial Israeli issue of whether Orthodox Jews should be drafted. Rather than rising to the level of a strategic dialogue, the secularist constituency of Kadima confronted the religious constituencies of the Likud coalition and failed to create a government able to devise a platform for decisive action.
This is Israel's crisis. It is not a sudden, life-threatening problem but instead is the product of unraveling regional strategies, a lack of confidence earned through failure and a political system incapable of unity on any particular course. Israel, a small country that always has used military force as its ultimate weapon, now faces a situation where the only possible use of military force -- against Iran -- is not only risky, it is not clearly linked to any of the main issues Israel faces other than the nuclear issue.
The French Third Republic was marked by a similar sense of self-regard overlaying a deep anxiety. This led to political paralysis and Paris' inability to understand the precise nature of the threat and to shape its response to it. Rather than deal with the issues at hand in the 1930s, the French relied on past glories to guide them. That didn't turn out very well.

PBA: 2012 draft live-cast

Saturday, August 11, 2012

MSSQL to FoxPro in ASP.net

In my recent project I need to create a routine that will append the data from sql to dbf foxpro table. Our MSSQL sql dump. The MS Access script that will append data from sql to foxpro has been in place but what the client wants is that an easy simple UI in asp page. So I end up creating a simple routine in asp.

Our MSSQL Temporary Table

CREATE TABLE [dbo].[temp_table_DownloadFruitCount](

[frtsiz] [int] NULL,

[variety] [int] NULL,

[FrtCnt] [int] NULL,

[AFW] [float] NULL,

[TonProc] [float] NULL,

[Eqtons] [int] NULL,

[Date] [datetime] NOT NULL

) ON [PRIMARY]

Our Stored Procedure

ALTER PROC [dbo].[FrtCnt_sp_DownloadFruitCount]

@dtdate DATETIME

IF OBJECT_ID('temp_table_DownloadFruitCount') IS NOT NULL DROP TABLE temp_table_DownloadFruitCount

SELECT a.fkfruitsize AS frtsiz,

a.fkvariety as variety,

SUM(a.HpineCount)FrtCnt,

MIN(d.afw) AFW,

ROUND(SUM(a.TonsProc),2) TonProc,

MIN(0) AS Eqtons,

a.dtdate AS [Date]

INTO temp_table_DownloadFruitCount

FROM FrtCnt_Tbl_Trans_FruitCount a

LEFT JOIN CanEffy_Tbl_Ref_FrtSize b ON a.FkFruitSize = b.PkFruitSize

LEFT JOIN FrtCnt_Tbl_Ref_Variety c ON a.fkvariety = c.pkvariety

LEFT JOIN FrtCnt_vw_GetAllAFWByFruitSize d ON a.fkFruitSize = d.fkFruitSize AND

a.dtdate = d.dtdate

WHERE a.dtdate = @dtdate

GROUP BY a.dtdate,

a.FkFruitSize,

a.fkvariety

ORDER BY

a.FkFruitSize,

a.fkvariety

-- FINAL QUERY

SELECT

a.frtsiz,

a.variety,

SUM(a.FrtCnt)AS FrtCnt,

MIN(a.afw) AS AFW,

ROUND(SUM(a.TonProc),2) TonProc,

MIN(a.Eqtons) AS EqTons,

a.[Date]

FROM temp_table_DownloadFruitCount a

GROUP BY a.[DATE],

a.frtsiz,

a.variety

ORDER BY

a.frtsiz,

a.variety

Basically, foxpro table and sql table has the same fieldname and type. In MS Access this is just easy

Private Sub cmdDownloadFC_Click()

'Author: Nathaniel Sumaya

'Purpose: Download FruitCount

'Date Created: 20120806 1143H

Dim dtdate As Date

dtdate = InputBox("Please input date")

If IsDate(dtdate) Then

Dim cmRs As New ADODB.Command

Set cmRs = New ADODB.Command

If ConnectSQL(True, "connectionstring", "catalog") Then

'----- DELETE

With cmRs

.ActiveConnection = conADO

'.CommandText = "sp_Operhr " & dtdate & "," & intYear & _

"," & intPeriodNo & "," & intWeekNo

.CommandText = "FrtCnt_sp_DownloadFruitCount '" & dtdate & "'"

.Execute

End With

'----- END OF DELETE ROUTINE

End If

Else

End If

'Delete FruitDUM

DeleteFruitDum

'AppendFruitDum

AppendFruitDum

End Sub

Sub DeleteFruitDum()

DoCmd.RunSQL ("DELETE FROM FRUITDUM")

End Sub

Sub AppendFruitDum()

Dim strsql As String

strsql = "INSERT INTO FRUITDUM SELECT * FROM dbo_temp_table_DownloadFruitCount "

DoCmd.RunSQL strsql

End Sub

Our Business Object Class

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

namespace FrtCnt.BO

{

/// <summary>

/// Summary description for FrtCnt_UploadFruitCount

/// </summary>

public class FrtCnt_UploadFruitCount

{

private int _frtsiz;

private int _variety;

private int _frtCnt;

private double _afw;

private double _tonsproc;

private int _eqtons;

private DateTime _dtdate;

public FrtCnt_UploadFruitCount()

{

}

public int FrtSize

{

get { return _frtsiz; }

set { _frtsiz = value; }

}

public int Variety

{

get { return _variety; }

set { _variety = value; }

}

public int FrtCnt

{

get { return _frtCnt; }

set { _frtCnt = value; }

}

public double AFW

{

get { return _afw; }

set { _afw = value; }

}

public double TonsProc

{

get { return _tonsproc; }

set { _tonsproc = value; }

}

public int EqTons

{

get { return _eqtons; }

set { _eqtons = value; }

}

public DateTime DtDdate

{

get { return _dtdate; }

set { _dtdate = value; }

}

using System;

using System.Collections.Generic;

using System.Web;

namespace FrtCnt.BO

{

/// <summary>

/// Summary description for FrtCnt_UploadFruitCountList

/// </summary>

public class FrtCnt_UploadFruitCountList:List<FrtCnt_UploadFruitCount>

{

public FrtCnt_UploadFruitCountList()

{

// TODO: Add constructor logic here

}

Our Business Logic Layer Class

using System;

using System.ComponentModel;

using FrtCnt.BO;

using FrtCnt.DAL;

namespace FrtCnt.BLL

{

/// <summary>

/// Summary description for FrtCnt_UploadFruitCountManager

/// </summary>

[DataObjectAttribute()]

public class FrtCnt_UploadFruitCountManager

{

public FrtCnt_UploadFruitCountManager()

{

// TODO: Add constructor logic here

}

[DataObjectMethod(DataObjectMethodType.Select)]

public static FrtCnt_UploadFruitCountList GetList(DateTime dtdate)

{

return FrtCnt_UploadFruitCountDB.GetList(dtdate);

}

//Display Routine

[DataObjectMethod(DataObjectMethodType.Select)]

public static FrtCnt_UploadFruitCountList GetListFromFoxPro(DateTime dtdate)

{

return FrtCnt_UploadFruitCountDB.GetListFromFoxPro(dtdate);

}

//routine for Insert

[DataObjectMethod(DataObjectMethodType.Update | DataObjectMethodType.Insert, true)]

public static FrtCnt_UploadFruitCountList SaveFruitCountToFoxPro(DateTime dtdate)

{

return FrtCnt_UploadFruitCountDB.SaveFruitCountToFoxPro(dtdate);

}

//routine for delete

[DataObjectMethod(DataObjectMethodType.Delete, true)]

public static bool Delete(FrtCnt_UploadFruitCount myFruitCount)

{

return FrtCnt_UploadFruitCountDB.Delete();

}

Our Data Access Layer Class

using System;

using System.Collections.Generic;

using System.Web;

using System.Data.Common;

using System.Data.SqlClient;

using System.Data;

using System.Data.Odbc;

using FrtCnt.BO;

using CanEffy.DAL; //for connectionstring

namespace FrtCnt.DAL

{

/// <summary>

/// Summary description for FrtCnt_UploadFruitCountDB

/// </summary>

public class FrtCnt_UploadFruitCountDB

{

public FrtCnt_UploadFruitCountDB()

{

// TODO: Add constructor logic here

}

#region PUBLIC METHODS

public static FrtCnt_UploadFruitCountList GetList(DateTime dtdate)

{

FrtCnt_UploadFruitCountList tempList = null;

using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString3))

{

SqlCommand myCommand = new SqlCommand("FrtCnt_sp_GetAllFruitCountBySize", myConnection);

myCommand.CommandType = CommandType.StoredProcedure;

myCommand.Parameters.AddWithValue("@dtdate", dtdate);

myConnection.Open();

using (SqlDataReader myReader = myCommand.ExecuteReader())

{

if (myReader.HasRows)

{

tempList = new FrtCnt_UploadFruitCountList();

while (myReader.Read())

{

tempList.Add(FillDataRecord(myReader));

}

myReader.Close();

}

myConnection.Close();

}

return tempList;

}

/// <summary>

/// Get the list from FoxPro Database

/// </summary>

/// <param name="dtdate">date of operations</param>

/// <returns>List of FruitCounts</returns>

public static FrtCnt_UploadFruitCountList GetListFromFoxPro(DateTime dtdate)

{

FrtCnt_UploadFruitCountList tempList = null;

//using (SqlConnection myConnection = new SqlConnection(AppConfiguration.FoxProConnect))

using (OdbcConnection myConnection = new OdbcConnection(AppConfiguration.FoxProConnect))

{

string strsql = "SELECT * FROM FRUITDUM WHERE DATE =#" + dtdate + "#";

OdbcCommand myCommand = new OdbcCommand(strsql , myConnection);

myCommand.CommandType = CommandType.Text;

myConnection.Open();

//using (SqlDataReader myReader = myCommand.ExecuteReader())

using (OdbcDataReader myReader = myCommand.ExecuteReader())

{

if (myReader.HasRows)

{

tempList = new FrtCnt_UploadFruitCountList();

while (myReader.Read())

{

tempList.Add(FillDataRecordFoxPro(myReader));

}

myReader.Close();

}

myConnection.Close();

}

return tempList;

}

/// <summary>

/// Delete FruitDum Data

/// </summary>

/// <returns></returns>

public static bool Delete()

{

int result = 0;

using (OdbcConnection myConnection = new OdbcConnection(AppConfiguration.FoxProConnect ))

{

string strsql = "DELETE FROM FRUITDUM";

OdbcCommand myCommand = new OdbcCommand(strsql, myConnection);

myCommand.CommandType = CommandType.Text;

//open the connection

myConnection.Open();

result = myCommand.ExecuteNonQuery();

myConnection.Close();

}

return result > 0;

}

public static FrtCnt_UploadFruitCountList SaveFruitCountToFoxPro(DateTime dtdate)

{

FrtCnt_UploadFruitCountList fcounts = GetList(dtdate); //new FrtCnt_UploadFruitCountList();

foreach (FrtCnt_UploadFruitCount fc in fcounts)

{

SaveFruitCount(fc.AFW, fc.DtDdate.ToShortDateString(), fc.EqTons, fc.FrtCnt, fc.FrtSize, fc.TonsProc, fc.Variety);

//Save(fc);

}

return fcounts;

}

private static void SaveFruitCount(double afw, string dtdate, double eqtons, int frtCount, int frtSize, double tProc, int frtVariety)

{

OdbcConnection con = new OdbcConnection(AppConfiguration.FoxProConnect);

string sqlinsert = " INSERT INTO FRUITDUM (AFW, [DATE], EQTONS, FRTCNT, FRTSIZ, TONPROC, VARIETY) VALUES " +

" ( " + afw +",#" + dtdate + "#," + eqtons + "," + frtCount + "," + frtSize + "," + tProc + "," + frtVariety + ")";

con.Open();

//OdbcCommand cmd = new OdbcCommand("insert into FruitDum('" + age + "','" + name + "')", con);

OdbcCommand cmd = new OdbcCommand(sqlinsert, con);

cmd.ExecuteNonQuery();

con.Close();

}

public static int Save(FrtCnt_UploadFruitCount myFruitCount)

{

string strsql = " INSERT INTO FRUITDUM (AFW, [DATE], EQTONS, FRTCNT, FRTSIZ, TONPROC, VARIETY) values " +

" (@afw, @dtdate, @eqtons, @frtCnt, @frtSize, @tonsProc, @variety)";

int result = 0;

using (OdbcConnection myConnection = new OdbcConnection(AppConfiguration.FoxProConnect))

{

using (OdbcCommand myCommand = new OdbcCommand(strsql, myConnection))

{

myCommand.CommandType = CommandType.Text;

//myCommand.Parameters.AddWithValue("@afw", myFruitCount.AFW);

//myCommand.Parameters.AddWithValue("@dtdate", myFruitCount.DtDdate);

//myCommand.Parameters.AddWithValue("@eqtons", myFruitCount.EqTons);

//myCommand.Parameters.AddWithValue("@frtCnt", myFruitCount.FrtCnt);

//myCommand.Parameters.AddWithValue("@frtSize", myFruitCount.FrtSize);

//myCommand.Parameters.AddWithValue("@tonsProc", myFruitCount.TonsProc);

//myCommand.Parameters.AddWithValue("@variety", myFruitCount.Variety);

OdbcParameter p = new OdbcParameter("@afw", SqlDbType.Float);

p.Value = myFruitCount.AFW;

myCommand.Parameters.Add(p);

p = new OdbcParameter("@dtdate", SqlDbType.Date);

p.Value = myFruitCount.DtDdate;

myCommand.Parameters.Add(p);

p = new OdbcParameter("@eqtons", SqlDbType.Int);

p.Value = myFruitCount.EqTons;

myCommand.Parameters.Add(p);

p = new OdbcParameter("@frtCnt", SqlDbType.Int);

p.Value = myFruitCount.FrtCnt;

myCommand.Parameters.Add(p);

p = new OdbcParameter("@frtSize", SqlDbType.Int);

p.Value = myFruitCount.FrtSize;

myCommand.Parameters.Add(p);

p = new OdbcParameter("@tonsProc", SqlDbType.Decimal);

p.Value = myFruitCount.TonsProc;

myCommand.Parameters.Add(p);

p = new OdbcParameter("@variety", SqlDbType.Int);

p.Value = myFruitCount.Variety;

myCommand.Parameters.Add(p);

myConnection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();

}

return result;

}

#endregion

#region PRIVATE METHODS

/// <summary>

/// Get Fruit Count Instance

/// </summary>

/// <param name="myDataRecord"></param>

/// <returns>Instance of Fruit Count</returns>

private static FrtCnt_UploadFruitCount FillDataRecord(IDataRecord myDataRecord)

{

FrtCnt_UploadFruitCount uploadedFCount = new FrtCnt_UploadFruitCount();

//frtsize

if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("frtsiz")))

{

uploadedFCount.FrtSize = myDataRecord.GetInt32(myDataRecord.GetOrdinal("frtsiz"));

}

//variety

if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("variety")))

{

uploadedFCount.Variety = myDataRecord.GetInt32(myDataRecord.GetOrdinal("variety"));

}

//FrtCnt

if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("FrtCnt")))

{

uploadedFCount.FrtCnt = myDataRecord.GetInt32(myDataRecord.GetOrdinal("FrtCnt"));

}

//AFW

if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("afw")))

{

uploadedFCount.AFW= myDataRecord.GetDouble(myDataRecord.GetOrdinal("afw"));

}

//TonsProc

if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TonProc")))

{

uploadedFCount.TonsProc = myDataRecord.GetDouble(myDataRecord.GetOrdinal("TonProc"));

}

//Eqtons

if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("eqtons")))

{

uploadedFCount.EqTons = myDataRecord.GetInt32(myDataRecord.GetOrdinal("eqtons"));

}

//date

if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("date")))

{

uploadedFCount.DtDdate = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("date"));

}

return uploadedFCount;

}

private static FrtCnt_UploadFruitCount FillDataRecordFoxPro(IDataRecord myDataRecord)

{

FrtCnt_UploadFruitCount uploadedFCount = new FrtCnt_UploadFruitCount();

//frtsize

if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("frtsiz")))

{

uploadedFCount.FrtSize = (Int32)myDataRecord.GetDouble(myDataRecord.GetOrdinal("frtsiz"));

}

//variety

if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("variety")))

{

uploadedFCount.Variety = (Int32)myDataRecord.GetDouble(myDataRecord.GetOrdinal("variety"));

}

//FrtCnt

if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("FrtCnt")))

{

uploadedFCount.FrtCnt = (Int32)myDataRecord.GetDouble(myDataRecord.GetOrdinal("FrtCnt"));

}

//AFW

if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("afw")))

{

uploadedFCount.AFW = myDataRecord.GetDouble(myDataRecord.GetOrdinal("afw"));

}

//TonsProc

if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TonProc")))

{

uploadedFCount.TonsProc = myDataRecord.GetDouble(myDataRecord.GetOrdinal("TonProc"));

}

//Eqtons

if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("eqtons")))

{

uploadedFCount.EqTons = (Int32)myDataRecord.GetDouble(myDataRecord.GetOrdinal("eqtons"));

}

//date

if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("date")))

{

uploadedFCount.DtDdate = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("date"));

}

return uploadedFCount;

}

#endregion

}

And Finally Our Application Layer Class

Code Behind

//export gridview data to excel

//http://www.c-sharpcorner.com/UploadFile/DipalChoksi/exportxl_asp2_dc11032006003657AM/exportxl_asp2_dc.aspx?ArticleID=000c64fb-8a22-414a-8247-984335aaa0eb

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data;

using System.Data.Common;

using System.Data.SqlClient;

using System.ComponentModel; //added to try some funnctionality

using FrtCnt.BO; //added to try some funnctionality

using FrtCnt.BLL; // Logic Layer

using System.Text;

using System.IO;

public partial class CanEffy_Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

private void ExportGridView1()

{

string attachment = "attachment; filename=fruitdum.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

this.gvUpload.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

public override void VerifyRenderingInServerForm(Control control)

{

}

private void PrepareGridViewForExport(Control gv)

{

LinkButton lb = new LinkButton();

Literal l = new Literal();

string name = String.Empty;

for (int i = 0; i < gv.Controls.Count; i++)

{

if (gv.Controls[i].GetType() == typeof(LinkButton))

{

l.Text = (gv.Controls[i] as LinkButton).Text;