public static SubscriptionsManager Import(string subscriptionsDataFile, PublishData publishSettings, X509Certificate2 certificate)
{
var subscriptionsManager = new SubscriptionsManager();
if (File.Exists(subscriptionsDataFile))
{
subscriptionsManager.ImportSubscriptionsFile(subscriptionsDataFile, certificate);
}
if (publishSettings != null)
{
foreach (var subscription in publishSettings.Items.Single().Subscription)
{
var subscriptionData = subscriptionsManager.Subscriptions.ContainsKey(subscription.Name)
? subscriptionsManager.Subscriptions[subscription.Name]
: new SubscriptionData { SubscriptionName = subscription.Name };
subscriptionData.SubscriptionId = subscription.Id;
subscriptionData.Certificate = certificate;
subscriptionData.ServiceEndpoint = publishSettings.Items.Single().Url;
subscriptionsManager.Subscriptions[subscriptionData.SubscriptionName] = subscriptionData;
}
}
return subscriptionsManager;
}
public static SubscriptionsManager Import(string subscriptionsDataFile, PublishData publishSettings, X509Certificate2 certificate)
{
var subscriptionsManager = new SubscriptionsManager();
if (File.Exists(subscriptionsDataFile))
{
subscriptionsManager.ImportSubscriptionsFile(subscriptionsDataFile, certificate);
}
if (publishSettings != null)
{
foreach (var subscription in publishSettings.Items.Single().Subscription)
{
var subscriptionData = subscriptionsManager.Subscriptions.ContainsKey(subscription.Name)
? subscriptionsManager.Subscriptions[subscription.Name]
: new SubscriptionData { SubscriptionName = subscription.Name };
subscriptionData.SubscriptionId = subscription.Id;
if (!string.IsNullOrEmpty(subscription.ManagementCertificate))
{
subscriptionData.Certificate = new X509Certificate2(Convert.FromBase64String(subscription.ManagementCertificate), string.Empty);
}
else
{
subscriptionData.Certificate = certificate;
}
if (!string.IsNullOrEmpty(subscription.ServiceManagementUrl))
{
subscriptionData.ServiceEndpoint = subscription.ServiceManagementUrl;
}
else
{
subscriptionData.ServiceEndpoint = publishSettings.Items.Single().Url;
}
subscriptionsManager.Subscriptions[subscriptionData.SubscriptionName] = subscriptionData;
}
}
return subscriptionsManager;
}
private void New(string subscriptionsDataFile, X509Certificate2 certificate, string serviceEndpoint)
{
Validate.ValidateStringIsNullOrEmpty(GlobalPaths.AzureDirectory, Resources.AzureDirectoryName);
Certificate = certificate;
SubscriptionManager = SubscriptionsManager.Import(subscriptionsDataFile, null, certificate);
ServiceConfiguration = new ServiceConfiguration { endpoint = serviceEndpoint };
PublishSettings = new PublishData();
var publishDataProfile = new PublishDataPublishProfile
{
Url = ServiceConfiguration.endpoint
};
if (Certificate != null)
{
publishDataProfile.ManagementCertificate = certificate.Thumbprint;
}
if (SubscriptionManager.Subscriptions != null &&
SubscriptionManager.Subscriptions.Count > 0)
{
var subscription = SubscriptionManager.Subscriptions.Values.First();
ServiceConfiguration.subscription = subscription.SubscriptionId;
ServiceConfiguration.subscriptionName = subscription.SubscriptionName;
publishDataProfile.Subscription = new [] {
new PublishDataPublishProfileSubscription
{
Id = subscription.SubscriptionId,
Name = subscription.SubscriptionName
}
};
}
PublishSettings.Items = new [] { publishDataProfile };
}
public static void AreEqualGlobalComponents(GlobalPathInfo paths, PublishData publishSettings, GlobalComponents actual)
{
AreEqualGlobalPathInfo(paths, actual.GlobalPaths);
}
public static SubscriptionsManager Import(string subscriptionsDataFile, PublishData publishSettings)
{
var certificate = new X509Certificate2(Convert.FromBase64String(publishSettings.Items[0].ManagementCertificate), string.Empty);
publishSettings.Items[0].ManagementCertificate = certificate.Thumbprint;
return Import(subscriptionsDataFile, publishSettings, certificate);
}
//---------------------------------------------------------/
// Method
//---------------------------------------------------------/
public void ExcelFile(string sheetName, PublishData pd, DataGridView dgv0, DataGridView dgv1, DataGridView dgv2)
{
editExcelSheet(sheetName, pd, dgv0, dgv1, dgv2);
}
// Wakamatsu 20170302
/// <summary>
/// 業務元帳出力
/// </summary>
/// <param name="pd">出力データ</param>
/// <param name="dgv">出力対象データグリッドビュー</param>
private void editTaskSummary(PublishData pd, DataGridView dgv)
{
oWSheet.Cell(3, 2).Value = pd.OfficeName + " " + pd.Department; // 部門 部署
oWSheet.Cell(3, 5).Value = pd.OrderStartDate.ToString("yyyy/MM/dd") +
"～" + pd.OrderEndDate.ToString("yyyy/MM/dd"); // 表示期間
oWSheet.Cell(5, 2).Value = pd.TaskCode; // 業務番号
oWSheet.Cell(5, 5).Value = pd.TaskName; // 業務名
oWSheet.Cell(7, 5).Value = pd.PartnerName; // 取引先名
oWSheet.Cell(7, 7).Value = pd.Note; // 工期
int WriteOffset = 12; // 書出し位置オフセット
bool EmptyFlag = true; // 空データグリッドビューフラグ
for (int i = 0; i < 8; i++)
{
// Wakamatu 20170307
//if( Convert.ToString( dgv.Rows[i].Cells[i].Value ) != "" )
if (Convert.ToString(dgv.Rows[0].Cells[i].Value) != "")
{
EmptyFlag = false;
}
}
if (EmptyFlag == false)
{
for (int i = 0; i < dgv.Rows.Count; i++)
{
using (IXLRange SetRange = oWSheet.Range("A11:I11"))
// テンプレートデータ行コピー/ペースト
SetRange.CopyTo(oWSheet.Cell(i + WriteOffset, 1));
if (Convert.ToString(dgv.Rows[i].Cells["ReportDate"].Value) != "") // 月日
{
oWSheet.Cell(i + WriteOffset, 1).Value = Convert.ToString(dgv.Rows[i].Cells["ReportDate"].Value);
oWSheet.Range(i + WriteOffset, 1, i + WriteOffset, 9).Style.Border.TopBorder = XLBorderStyleValues.Hair;
}
if (Convert.ToString(dgv.Rows[i].Cells["SlipNo"].Value) != "") // 伝票No.
{
oWSheet.Cell(i + WriteOffset, 2).Value = Convert.ToString(dgv.Rows[i].Cells["SlipNo"].Value);
oWSheet.Cell(i + WriteOffset, 2).Style.Border.TopBorder = XLBorderStyleValues.Hair;
oWSheet.Cell(i + WriteOffset, 9).Style.Border.TopBorder = XLBorderStyleValues.Hair;
}
if (Convert.ToString(dgv.Rows[i].Cells["ItemCode"].Value) != "") // コード
{
oWSheet.Cell(i + WriteOffset, 3).Value = Convert.ToString(dgv.Rows[i].Cells["ItemCode"].Value);
}
else
{
oWSheet.Cell(i + WriteOffset, 2).Style.Border.RightBorder = XLBorderStyleValues.None;
oWSheet.Cell(i + WriteOffset, 3).Style.Border.LeftBorder = XLBorderStyleValues.None;
oWSheet.Cell(i + WriteOffset, 3).Style.Border.RightBorder = XLBorderStyleValues.None;
oWSheet.Cell(i + WriteOffset, 4).Style.Border.LeftBorder = XLBorderStyleValues.None;
oWSheet.Cell(i + WriteOffset, 5).Style.Border.RightBorder = XLBorderStyleValues.None;
oWSheet.Cell(i + WriteOffset, 6).Style.Border.LeftBorder = XLBorderStyleValues.None;
oWSheet.Cell(i + WriteOffset, 7).Style.Border.RightBorder = XLBorderStyleValues.None;
oWSheet.Cell(i + WriteOffset, 8).Style.Border.LeftBorder = XLBorderStyleValues.None;
}
oWSheet.Range(i + WriteOffset, 3, i + WriteOffset, 8).Style.Border.TopBorder = XLBorderStyleValues.Hair;
if (Convert.ToString(dgv.Rows[i].Cells["Item"].Value) != "") // 名称
{
oWSheet.Cell(i + WriteOffset, 4).Value = Convert.ToString(dgv.Rows[i].Cells["Item"].Value);
}
if (Convert.ToString(dgv.Rows[i].Cells["Quantity"].Value) != "") // 数量
{
oWSheet.Cell(i + WriteOffset, 6).Value = Convert.ToString(dgv.Rows[i].Cells["Quantity"].Value);
}
if (Convert.ToString(dgv.Rows[i].Cells["UnitPrice"].Value) != "") // 単価
{
oWSheet.Cell(i + WriteOffset, 7).Value = Convert.ToString(dgv.Rows[i].Cells["UnitPrice"].Value);
}
if (Convert.ToString(dgv.Rows[i].Cells["Cost"].Value) != "") // 金額
{
oWSheet.Cell(i + WriteOffset, 8).Value = Convert.ToString(dgv.Rows[i].Cells["Cost"].Value);
}
if (Convert.ToString(dgv.Rows[i].Cells["Balance"].Value) != "") // 累計
{
oWSheet.Cell(i + WriteOffset, 9).Value = Convert.ToString(dgv.Rows[i].Cells["Balance"].Value);
}
// 行の高さ設定
oWSheet.Row(i + WriteOffset).Height = 21.00;
if (i == dgv.Rows.Count - 1)
{
// 最終データのみ罫線の設定
oWSheet.Range(i + WriteOffset, 1, i + WriteOffset, 9).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
}
}
}
// 先頭データのみ罫線の設定
oWSheet.Range(WriteOffset, 1, WriteOffset, 9).Style.Border.TopBorder = XLBorderStyleValues.Thin;
// テンプレート行削除
oWSheet.Row(11).Delete();
oWSheet.PageSetup.FitToPages(1, 0);
}
// 出来高請求書
private void editAccountsVolumeInvoice(PublishData pd, DataGridView dgv)
{
//MessageBox.Show("Excel書込み開始");
editSender(pd, 1, 10);
oWSheet.Cell(2, 1).Value = pd.PartnerName + " 御中";
oWSheet.Cell(4, 3).Value = pd.TaskName;
readyExcelRows(dgv, 3, 13, 7, 9);
int maxRowsCount = dgvRowsCount(dgv, 2, 15);
const int SR = 8; // Excel Sheet Start Row No.
string wMonth = "";
string wDay = "";
for (int i = 0; i < maxRowsCount; i++)
{
if (Convert.ToString(dgv.Rows[i].Cells["Month"].Value) != wMonth)
{
oWSheet.Cell(SR + i, 1).Value = Convert.ToString(dgv.Rows[i].Cells["Month"].Value);
wMonth = Convert.ToString(dgv.Rows[i].Cells["Month"].Value);
}
if (Convert.ToString(dgv.Rows[i].Cells["Day"].Value) != wDay)
{
oWSheet.Cell(SR + i, 2).Value = Convert.ToString(dgv.Rows[i].Cells["Day"].Value);
wDay = Convert.ToString(dgv.Rows[i].Cells["Day"].Value);
}
oWSheet.Cell(SR + i, 3).Value = Convert.ToString(dgv.Rows[i].Cells["Item"].Value);
oWSheet.Cell(SR + i, 4).Value = Convert.ToString(dgv.Rows[i].Cells["ItemDetail"].Value);
// 契約
if (Convert.ToString(dgv.Rows[i].Cells["CQuantity"].Value) != "")
{
oWSheet.Cell(SR + i, 5).Value = Convert.ToDecimal(dgv.Rows[i].Cells["CQuantity"].Value);
}
oWSheet.Cell(SR + i, 6).Value = Convert.ToString(dgv.Rows[i].Cells["Unit"].Value);
if (Convert.ToString(dgv.Rows[i].Cells["CCost"].Value) != "")
{
oWSheet.Cell(SR + i, 7).Value = Convert.ToDecimal(dgv.Rows[i].Cells["CCost"].Value);
}
if (Convert.ToString(dgv.Rows[i].Cells["CAmount"].Value) != "")
{
oWSheet.Cell(SR + i, 8).Value = Convert.ToDecimal(dgv.Rows[i].Cells["CAmount"].Value);
}
// 前回までの累積
if (Convert.ToString(dgv.Rows[i].Cells["SQuantity"].Value) != "")
{
oWSheet.Cell(SR + i, 9).Value = Convert.ToDecimal(dgv.Rows[i].Cells["SQuantity"].Value);
}
if (Convert.ToString(dgv.Rows[i].Cells["SAmount"].Value) != "")
{
oWSheet.Cell(SR + i, 10).Value = Convert.ToDecimal(dgv.Rows[i].Cells["SAmount"].Value);
}
// 今回
if (Convert.ToString(dgv.Rows[i].Cells["Quantity"].Value) != "")
{
oWSheet.Cell(SR + i, 11).Value = Convert.ToDecimal(dgv.Rows[i].Cells["Quantity"].Value);
}
if (Convert.ToString(dgv.Rows[i].Cells["Amount"].Value) != "")
{
oWSheet.Cell(SR + i, 12).Value = Convert.ToDecimal(dgv.Rows[i].Cells["Amount"].Value);
}
// 残
if (Convert.ToString(dgv.Rows[i].Cells["RAmount"].Value) == "")
{
oWSheet.Cell(SR + i, 13).Value = "";
}
else
{
oWSheet.Cell(SR + i, 13).Value = Convert.ToDecimal(dgv.Rows[i].Cells["RAmount"].Value);
}
}
}
// 予算書エクセル出力
private void editPlanning(PublishData pd)
{
//MessageBox.Show("Excel書込み開始");
oWSheet.Cell(26, 2).Value = pd.TaxRate;
oWSheet.Cell(26, 3).Value = pd.OthersCostRate;
oWSheet.Cell(26, 4).Value = pd.AdminCostRate;
oWSheet.Cell(2, 1).Value = pd.TaskCode;
oWSheet.Cell(2, 2).Value = pd.TaskName;
oWSheet.Cell(2, 5).Value = pd.TaskPlace;
oWSheet.Cell(2, 7).Value = pd.PartnerName;
oWSheet.Cell(2, 10).Value = pd.LeaderName;
oWSheet.Cell(2, 12).Value = pd.SalesMName;
if (pd.Sales0 > 0)
{
oWSheet.Cell(7, 2).Value = pd.Sales0;
}
if (pd.Sales1 > 0)
{
oWSheet.Cell(9, 2).Value = pd.Sales1;
}
if (pd.Sales2 > 0)
{
oWSheet.Cell(11, 2).Value = pd.Sales2;
}
oWSheet.Cell(8, 5).Value = pd.ContractDate;
oWSheet.Cell(10, 5).Value = pd.StartDate;
oWSheet.Cell(12, 5).Value = pd.EndDate;
if (pd.Direct0 > 0)
{
oWSheet.Cell(15, 3).Value = pd.Direct0;
}
if (pd.Direct1 > 0)
{
oWSheet.Cell(15, 4).Value = pd.Direct1;
}
if (pd.Direct2 > 0)
{
oWSheet.Cell(15, 5).Value = pd.Direct2;
}
if (pd.OutS0 > 0)
{
oWSheet.Cell(16, 3).Value = pd.OutS0;
}
if (pd.OutS1 > 0)
{
oWSheet.Cell(16, 4).Value = pd.OutS1;
}
if (pd.OutS2 > 0)
{
oWSheet.Cell(16, 5).Value = pd.OutS2;
}
if (pd.Matel0 > 0)
{
oWSheet.Cell(17, 3).Value = pd.Matel0;
}
if (pd.Matel1 > 0)
{
oWSheet.Cell(17, 4).Value = pd.Matel1;
}
if (pd.Matel2 > 0)
{
oWSheet.Cell(17, 5).Value = pd.Matel2;
}
}
// Wakamatsu
private void editEstimateTop(PublishData pd)
{
//MessageBox.Show("Excel書込み開始");
editEstimateCommon(pd);
oWSheet.Cell(11, 5).Value = pd.Note;
}
// Wakamatsu
//----------------------------------------------------------------------
// SubRoutine
//----------------------------------------------------------------------
private void editExcelSheet(string sheetName, PublishData pd, DataGridView dgv, DataGridView dgv1, DataGridView dgv2)
{
string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";
Cursor.Current = Cursors.WaitCursor; // マウスカーソルを砂時計(Wait)
using (oWBook = new XLWorkbook(fileName))
{
oWSheet = oWBook.Worksheet(sheetName);
switch (sheetName)
{
case "EstimateTop":
editEstimateTop(pd);
oWSheet = oWBook.Worksheet("EstimateCont");
editEstimateCont(dgv);
break;
case "EstimateCopy":
editEstimateCopy(pd);
oWSheet = oWBook.Worksheet("EstimateCont");
editEstimateCont(dgv);
break;
case "Planning":
editPlanning(pd);
break;
case "PlanningCont":
editPlanningCont(pd, dgv);
break;
case "OsOrder":
editOutsourceOrder(pd);
editOutsourceConfirm(pd);
break;
case "OsConfirm":
editOutsourceConfirm(pd);
break;
case "OsContent":
editOutsourceContent(pd, dgv);
break;
case "OsARegular":
editAccountsRegular(pd, dgv, dgv1, dgv2);
break;
case "OsAContract":
editAccountsContract(pd, dgv);
break;
case "Invoice":
editAccountsInvoice(pd, dgv);
break;
case "VolumeInvoice":
editAccountsVolumeInvoice(pd, dgv);
break;
// Wakamatsu 20170302
case "TaskSummary":
editTaskSummary(pd, dgv);
break;
// Wakamatsu 20170302
default:
break;
}
oWBook.SaveAs(tempFile); // Excel保存
}
Cursor.Current = Cursors.Default; // マウスカーソルを戻す
System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel
}
protected void btnSaveUpdate_Click(object sender, EventArgs e)
{
if (!Page.IsValid)
{
return;
}
try
{
PublishData ob = new PublishData();
Business b = new Business();
string CreatedBy = Session["UserId"].ToString();
string ISSN = txtboxID.Text;
string QJYear = TextBoxYearJAQ.SelectedValue;
//string QValue = DropDownQuartile.SelectedValue.ToString();
string QValue1 = DropDownQuartile.SelectedItem.Value;
string QValue2 = DropDownQuartile.SelectedItem.Text;
if (QValue2 == "-Select-")
{
string CloseWindow1 = "alert('Please select the Quartile Value')";
ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "CloseWindow", CloseWindow1, true);
return;
}
//string QJYear=
ob.QYear = Convert.ToUInt16(QJYear);
ob.PublisherOfJournal = ISSN;
string QId = QValue1;
int result = 0;
ob = b.checkJournalLinkM(ob.PublisherOfJournal, ob.QYear);
string qr = ob.Jquartile;
if ((ob.Jquartile) == null)
{
result = b.InsertJournalQuartileMap(ISSN, Convert.ToInt16(QJYear), QId, CreatedBy);
ClientScript.RegisterStartupScript(Page.GetType(), "validation1", "<script language='javascript'>alert('Quartile Value saved Succesfully')</script>");
popupPanelJournal.Visible = false;
return;
}
if (result == 0)
{
if (ob.Jquartile == Convert.ToString(QId))
{
//ob = b.InsertJournalQuartileTracker(ISSN, Convert.ToUInt16(QJYear), ob.Jquartile, QId, Session["UserId"]);
ClientScript.RegisterStartupScript(Page.GetType(), "validation1", "<script language='javascript'>alert('Please select the Quartile Value')</script>");
popupPanelJournal.Visible = false;
return;
}
else if (ob.Jquartile != Convert.ToString(QId))
{
ob = b.InsertJournalQuartileTracker(ISSN, Convert.ToUInt16(QJYear), ob.Jquartile, QId, Session["UserId"]);
ClientScript.RegisterStartupScript(Page.GetType(), "validation1", "<script language='javascript'>alert('Quartile Value Updated Succesfully')</script>");
popupPanelJournal.Visible = false;
return;
}
}
}
catch (Exception ex)
{
log.Error("Inside Catch Block Of Manage Journal" + ex.Message);
log.Error(ex.StackTrace);
ClientScript.RegisterStartupScript(Page.GetType(), "validation1", "<script language='javascript'>alert('Error!!!!!!!!!')</script>");
}
}
protected void JournalIDTextChanged(object sender, EventArgs e)
{
ArrayList list = new ArrayList();
//txtboxYear.Text = "";
txtboxTitle.Text = "";
//cblActiveyear.ClearSelection();
popupPanelJournal.Visible = false;
JournalValueObj.JournalID = txtboxID.Text;
// JournalValueObj.year = txtBoxYear.Text;
JournalData j = new JournalData();
j = B.JournalEntryCheckExistance(JournalValueObj);
if (j.jid != null)
{
log.Debug("inside --JournalIDTextChanged--update publish" + j.jid);
txtboxTitle.Text = j.name;
string year = DateTime.Now.Year.ToString();
int Jyear = Convert.ToInt32(year) - 1;
list = B.SelectActiveYear(JournalValueObj);
//for (int i = 0; i < list.Count; i++)
//{
// //ListItem currentCheckBox = cblActiveyear.Items.FindByValue(list[i].ToString());
// //if (currentCheckBox != null)
// //{
// // currentCheckBox.Selected = true;
// //}
//}
PublishData obj = new PublishData();
string jyear = TextBoxYearJAQ.SelectedItem.Text;
obj = B.checkQuartileValue(txtboxID.Text, jyear);
txtquartileID.Text = Convert.ToString(obj.Jquartile);
IncentiveBusiness ince_obj = new IncentiveBusiness();
obj = ince_obj.getquartileName(txtquartileID.Text);
//txtquartile.Text = obj.Name;
//DropDownQuartile.DataSourceID = "SqlDataSourceQuartileM";
//DropDownQuartile.DataTextField = "Name";
//DropDownQuartile.DataValueField = "Id";
//DropDownQuartile.DataBind();
DropDownQuartile.Items.Clear();
DropDownQuartile.Items.Add(new ListItem("-Select-", "0", true));
DropDownQuartile.DataSourceID = "SqlDataSourceQuartileM";
DropDownQuartile.DataBind();
if (txtquartileID.Text != "")
{
DropDownQuartile.SelectedValue = txtquartileID.Text;
}
//DropDownQuartile.SelectedItem.Value = txtquartileID.Text;
}
else
{
log.Debug("inside --JournalIDTextChanged--New Publish Id" + j.jid);
btnSaveUpdate.Enabled = true;
ClientScript.RegisterStartupScript(Page.GetType(), "validation1", "<script language='javascript'>alert('New Entry..Please enter the details !!!!')</script>");
txtboxTitle.Enabled = true;
}
}
