public void get_ac_accoun(string db)
{
AdsConnection adsconn = new AdsConnection("Data Source=\\\\192.168.5.5:6262\\bob50\\BOB50DATA\\" + db + ";ServerType=REMOTE;TableType=ADT;LockMode=PROPRIETARY;CharType=ADS_ANSI;SecurityMode=IGNORERIGHTS");
AdsCommand adscmd;
AdsDataReader adsreader;
//SqlConnection sqlconn = new SqlConnection("Data Source=srvdwh;Initial Catalog=Staging;User ID=dwh_writer;Password=GZXyJmTdKNfx7C4j;");
SqlConnection sqlconn = new SqlConnection("Data Source=SRVSQL\\FISODWH;Initial Catalog=Staging;User ID=dwh_writer;Password=GZXyJmTdKNfx7C4j;");
SqlCommand sqlcmd;
try
{
adsconn.Open();
sqlconn.Open();
adscmd = adsconn.CreateCommand();
sqlcmd = sqlconn.CreateCommand();
adscmd.CommandText = "SELECT '" + db + "' AS DB,AID,AISTITLE,HEADING1 FROM ac_accoun";
adsreader = adscmd.ExecuteReader();
sqlcmd.CommandText = "DELETE FROM dbo.ac_accoun WHERE DB = '" + db + "'";
sqlcmd.ExecuteNonQuery();
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlconn))
{
bulkCopy.DestinationTableName = "dbo.ac_accoun";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(adsreader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
}
}
adsconn.Close();
AdsConnection.FlushConnectionPool();
sqlconn.Close();
}
catch (AdsException e)
{
Console.WriteLine(e.Message);
}
}
private void versPressepapierToolStripMenuItem_Click(object sender, EventArgs e)
{
AdsConnection adsconn = new AdsConnection("Data Source=\\\\192.168.5.5:6262\\bob50\\BOB50DATA\\" + cB_Customer.Text.Substring(0, 6) + ";ServerType=REMOTE;TableType=ADT;LockMode=PROPRIETARY;CharType=ADS_ANSI;SecurityMode=IGNORERIGHTS");
AdsCommand adscmd;
AdsDataReader adsreader;
adsconn.Open();
adscmd = adsconn.CreateCommand();
//adscmd.CommandText = "SELECT HDOCDATE,CASE WHEN HID IN ('40110000') THEN 'C' WHEN HID IN ('44111000 ') THEN 'F' ELSE 'G' END AS Type, CASE WHEN HID IN ('40110000','44111000 ') THEN 'DIVERS' ELSE HID END AS HID, HAMOUNT * -1 AS HAMOUNT, 'Annulation '+RTRIM(HREM) AS HREM FROM ac_ahisto WHERE HDOCDATE BETWEEN '" + dP_periodStart.Value.Date.ToString("yyyy-MM-dd") + "' AND '"+ dP_periodEnd.Value.Date.ToString("yyyy-MM-dd")+"'";
adscmd.CommandText = "SELECT '31/12/'+CAST(YEAR(HDOCDATE) AS SQL_CHAR(4))+' 00:00:00' AS BOBDATE,CASE WHEN HID IN ('40110000') THEN 'C' WHEN HID IN ('44111000 ') THEN 'F' ELSE 'G' END AS TYPE, CASE WHEN HID IN ('40110000','44111000 ') THEN 'DIVERS' ELSE HID END AS ACCOUNT, SUM(HAMOUNT * -1) AS HAMOUNT, 'Annulation '+RTRIM(HREM) AS COMMENT FROM ac_ahisto WHERE HDOCDATE BETWEEN '" + dP_periodStart.Value.Date.ToString("yyyy-MM-dd") + "' AND '" + dP_periodEnd.Value.Date.ToString("yyyy-MM-dd") + "' GROUP BY BOBDATE,Type,ACCOUNT,COMMENT";
String clipbString;
clipbString = "";
adsreader = adscmd.ExecuteReader();
while (adsreader.Read())
{
clipbString += adsreader.GetValue(0) + "\t" + adsreader.GetValue(1) + "\t" + adsreader.GetValue(2) + "\t" + adsreader.GetValue(3) + "\t" + adsreader.GetValue(4) + "\n";
}
adsconn.Close();
AdsConnection.FlushConnectionPool();
SqlConnection sqlconn = new SqlConnection("Data Source=SRVSQL\\FISODWH;Initial Catalog=FisoDWH;User ID=dwh_writer;Password=GZXyJmTdKNfx7C4j;");
sqlconn.Open();
int periodEnd;
int periodStart;
periodStart = dP_periodStart.Value.Year * 100 + dP_periodStart.Value.Month;
periodEnd = dP_periodEnd.Value.Year * 100 + dP_periodEnd.Value.Month;
SqlCommand sqlcmd = new SqlCommand("SELECT DATEFROMPARTS(Year,12,31) AS Date, CASE WHEN Account IN ('40110000') THEN 'C' WHEN Account IN ('44111000 ') THEN 'F' ELSE 'G' END AS Type, CASE WHEN Account IN ('40110000','44111000 ') THEN 'DIVERS' ELSE Account END AS Account, SUM(Amount) AS Amount, 'Importation du '+ FORMAT(GETDATE(),'dd/MM/yyyy','fr-fr') AS Comment FROM [dbo].[Account_Balance] WHERE Customer_Code = '" + cB_Customer.Text.Substring(0, 6) + "' AND Year*100+Month BETWEEN " + periodStart.ToString() + " AND " + periodEnd.ToString() + "GROUP BY DATEFROMPARTS([Year],12, 31), Account ORDER BY Account", sqlconn);
try
{
var reader = sqlcmd.ExecuteReader();
while (reader.Read())
{
clipbString += reader.GetValue(0) + "\t" + reader.GetValue(1) + "\t" + reader.GetValue(2) + "\t" + reader.GetValue(3) + "\t" + reader.GetValue(4) + "\n";
}
Clipboard.SetText(clipbString.ToString());
}
catch
{ }
sqlconn.Close();
}
public void getData(string db, int fromYear, int toYear)
{
AdsConnection adsconn = new AdsConnection("Data Source=\\\\192.168.5.5:6262\\bob50\\BOB50DATA\\" + db + ";ServerType=REMOTE;TableType=ADT;LockMode=PROPRIETARY;CharType=ADS_ANSI;SecurityMode=IGNORERIGHTS");
AdsCommand adscmd;
AdsDataReader adsreader;
//SqlConnection sqlconn = new SqlConnection("Data Source=srvdwh;Initial Catalog=FisoDWH;User ID=dwh_writer;Password=GZXyJmTdKNfx7C4j;");
SqlConnection sqlconn = new SqlConnection("Data Source=SRVSQL\\FISODWH;Initial Catalog=FisoDWH;User ID=dwh_writer;Password=GZXyJmTdKNfx7C4j;");
SqlCommand sqlcmd;
adsconn.Open();
adscmd = adsconn.CreateCommand();
try
{
//adscmd.CommandText = "SELECT '"+db+"'+HID AS Account_ID,'"+db+ "' AS Customer_Code,HID AS Account,YR.YEAR AS Year,CASE WHEN HMONTH=0 THEN MONTH(HDOCDATE) ELSE HMONTH END AS Month,ROUND(SUM(HAMOUNT),2) AS Amount FROM ac_ahisto AH INNER JOIN (SELECT HFYEAR,MAX(HYEAR) AS YEAR FROM ac_ahisto GROUP BY HFYEAR)YR ON AH.HFYEAR=YR.HFYEAR GROUP BY HID,YR.YEAR,Month";
adscmd.CommandText = "SELECT '" + db + "'+HID AS Account_ID,'" + db + "' AS Customer_Code,HID AS Account, RTRIM(HFYEAR) AS Fiscal_Year,CASE WHEN HMONTH = 0 THEN Year(HDOCDATE) ELSE HYEAR END AS Year,CASE WHEN HMONTH = 0 THEN Month(HDOCDATE) ELSE HMONTH END AS Month,ROUND(SUM(HAMOUNT),2) AS Amount, COALESCE(COST_ENTITE,'') AS Cost_Center FROM ac_ahisto AH WHERE CASE WHEN HMONTH = 0 THEN Year(HDOCDATE) ELSE HYEAR END BETWEEN " + fromYear + " AND " + toYear + " GROUP BY HID,HFYEAR,Year,Month,COST_ENTITE";
adsreader = adscmd.ExecuteReader();
}
catch
{
adscmd.CommandText = "SELECT '" + db + "'+HID AS Account_ID,'" + db + "' AS Customer_Code,HID AS Account, RTRIM(HFYEAR) AS Fiscal_Year,CASE WHEN HMONTH = 0 THEN Year(HDOCDATE) ELSE HYEAR END AS Year,CASE WHEN HMONTH = 0 THEN Month(HDOCDATE) ELSE HMONTH END AS Month,ROUND(SUM(HAMOUNT),2) AS Amount, '' AS Cost_Center FROM ac_ahisto AH WHERE CASE WHEN HMONTH = 0 THEN Year(HDOCDATE) ELSE HYEAR END BETWEEN " + fromYear + " AND " + toYear + " GROUP BY HID,HFYEAR,Year,Month";
adsreader = adscmd.ExecuteReader();
}
try {
/*
* while (adsreader.Read())
* {
* Console.WriteLine(adsreader.GetValue(0) + "\t" + adsreader.GetValue(1) + "\t" + adsreader.GetValue(2) + "\t" + adsreader.GetValue(3) + "\t" + adsreader.GetValue(4) + "\t" + adsreader.GetValue(5) + "\t" + adsreader.GetValue(6));
* }
*/
sqlconn.Open();
sqlcmd = sqlconn.CreateCommand();
sqlcmd.CommandText = "DELETE FROM dbo.account_balance WHERE Customer_Code = '" + db + "' AND Year BETWEEN " + fromYear + " AND " + toYear;
sqlcmd.ExecuteNonQuery();
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlconn))
{
bulkCopy.DestinationTableName =
"dbo.account_balance";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(adsreader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
}
}
SqlCommand ExecJob = new SqlCommand();
ExecJob.CommandType = CommandType.StoredProcedure;
ExecJob.CommandText = "dbo.sp_update_bs_hier";
ExecJob.Connection = sqlconn;
using (sqlconn)
{
using (ExecJob)
{
ExecJob.ExecuteNonQuery();
}
}
adsconn.Close();
AdsConnection.FlushConnectionPool();
sqlconn.Close();
}
catch (AdsException e)
{
MessageBox.Show(e.Message);
}
}