private string GenerateSpreadsheetforGroupedWorksheet(long ScopeId, HttpContext context)
{
String result = String.Empty;
ScopeServices objScopeService = new ScopeServices();
objScopeService.SQLConnection = ConnectDb.SQLConnection;
ManagementService objManagementService = new ManagementService();
objManagementService.SQLConnection = ConnectDb.SQLConnection;
Scope objScope = objScopeService.GetScopeByScopeId(ScopeId);
Project objProject = objManagementService.GetProjectByProjectId(objScope.ProjectId);
DataSet dsQuery = new DataSet();
int intFootTotalStartRow;
int intFootTotalStartColum;
int intCompanyInfoStartRow;
int intCompanyInfoStartColum;
String strExcelFile;
String strNewExcelFileName;
DataSet dsProjectGroup = new DataSet();
String strClaimNo = "";
String strClaimant = "";
String strSiteLocation = "";
String strEQRSupervisor = "";
String strContractorEmail = "";
String strScopeDate = "";
String strContractor = "";
String strAddress = "";
String strAccreditationNo = "";
String strContractorPhone = "";
String strGSTNo = "";
String strTotalPrice = "";
DataSet dsProject = new DataSet();
ProjectOwner projectOwner = new ProjectOwner();
String strLogo = "";
dsProjectGroup = objScopeService.GetProjectGroupsByProjectOwnerId(objProject.ProjectOwnerId);
dsProject = objManagementService.GetProjectInfoByProjectId(objScope.ProjectId);
//get project group email
if (dsProjectGroup.Tables.Count > 0)
{
if (dsProjectGroup.Tables[0].Rows.Count > 0)
{
if (!Convert.IsDBNull(dsProjectGroup.Tables[0].Rows[0]["Email"]))
{
strContractorEmail = dsProjectGroup.Tables[0].Rows[0]["Email"].ToString();
}
}
}
//get claimant detail
if (!Convert.IsDBNull(dsProject.Tables[0].Rows[0]["EQCClaimNumber"]))
{
strClaimNo = dsProject.Tables[0].Rows[0]["EQCClaimNumber"].ToString();
}
if (!Convert.IsDBNull(dsProject.Tables[0].Rows[0]["ScopeDate"]))
{
strScopeDate = dsProject.Tables[0].Rows[0]["ScopeDate"].ToString();
}
if (!Convert.IsDBNull(dsProject.Tables[0].Rows[0]["ScopeDate"]))
{
strScopeDate = Convert.ToDateTime(dsProject.Tables[0].Rows[0]["ScopeDate"]).ToString("dd/MM/yyyy");
}
if (!Convert.IsDBNull(dsProject.Tables[0].Rows[0]["Address"]))
{
strSiteLocation = dsProject.Tables[0].Rows[0]["Address"].ToString();
}
if (!Convert.IsDBNull(dsProject.Tables[0].Rows[0]["ContactName"]))
{
strClaimant = dsProject.Tables[0].Rows[0]["ContactName"].ToString();
}
//get project owner detail
projectOwner = objManagementService.GetProjectOwnerByProjectOwnerId(objProject.ProjectOwnerId);
strEQRSupervisor = projectOwner.EQRSupervisor;
strContractor = projectOwner.Name;
strContractorPhone = projectOwner.Contact1;
strAddress = projectOwner.Address;
strGSTNo = projectOwner.GSTNumber;
strAccreditationNo = projectOwner.AccreditationNumber;
String ApprovedexcludeGstCost = objScope.Cost.ToString("c");
String ApprovedGSTCost = (objScope.Total - objScope.Cost).ToString("c");
String ApprovedInGSTCost = objScope.Total.ToString("c");
String GrandExGSTCost = (objScope.Cost1 + objScope.Cost).ToString("c");
String GrandInGSTCost = (objScope.Total1 + objScope.Total).ToString("c");
strTotalPrice = ApprovedInGSTCost;
//Company Logo
strLogo = String.Format("{0}/Images/{1}/{2}", "http://koreprojects.com", projectOwner.Identifier, projectOwner.Logo);
strNewExcelFileName = String.Empty;
String strExcelFileNameTemp = Strings.StrConv(strSiteLocation, VbStrConv.ProperCase);
int ascChar;
for (int index = 0; index < strExcelFileNameTemp.Length - 1; index++)
{
ascChar = Strings.Asc(strExcelFileNameTemp[index]);
if ((ascChar >= 65 && ascChar <= 90) || (ascChar >= 97 && ascChar <= 122) || (ascChar >= 48 && ascChar <= 57))
{
strNewExcelFileName = strNewExcelFileName + strExcelFileNameTemp[index];
}
else
{
strNewExcelFileName = strNewExcelFileName + "-";
}
}
do
{
strNewExcelFileName = Strings.Replace(strNewExcelFileName, "--", "-");
} while (strNewExcelFileName.Contains("--"));
if (strNewExcelFileName.Substring(0, 1) == "-")
{
strNewExcelFileName = strNewExcelFileName.Substring(1);
}
if (strNewExcelFileName.Substring(strNewExcelFileName.Length - 1) == "-")
{
strNewExcelFileName = strNewExcelFileName.Substring(0, strNewExcelFileName.Length - 1);
}
if (strNewExcelFileName.Length > 20)
{
strNewExcelFileName = strNewExcelFileName.Substring(0, 20);
}
strExcelFile = String.Format("../Downloads/{0}/{1}.xls", "ExcelExport", strNewExcelFileName);
if ((!System.IO.Directory.Exists(String.Format("../Downloads/{0}", "ExcelExport"))))
{
System.IO.Directory.CreateDirectory(String.Format("../Downloads/{0}", "ExcelExport"));
}
WorkSheet workSheet = new WorkSheet();
//initialzing the cellexport tool
cellExport = new Spire.DataExport.XLS.CellExport();
cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
//culture format setting
cellExport.DataFormats.CultureName = "en-NZ";
cellExport.DataFormats.Currency = "#,###,##0.00";
cellExport.DataFormats.DateTime = "dd/MM/yyyy H:mm";
cellExport.DataFormats.Float = "#,###,##0.00";
cellExport.DataFormats.Integer = "#,###,##0";
cellExport.DataFormats.Time = "H:mm";
//set up file name and location
cellExport.FileName = strExcelFile;
//outlook formating
cellExport.SheetOptions.AggregateFormat.Font.Name = "Arial";
cellExport.SheetOptions.CustomDataFormat.Font.Name = "Arial";
cellExport.SheetOptions.DefaultFont.Name = "Arial";
cellExport.SheetOptions.FooterFormat.Font.Name = "Arial";
cellExport.SheetOptions.HeaderFormat.Font.Name = "Arial";
cellExport.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
cellExport.SheetOptions.HyperlinkFormat.Font.Name = "Arial";
cellExport.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
cellExport.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
cellExport.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
cellExport.SheetOptions.NoteFormat.Font.Bold = true;
cellExport.SheetOptions.NoteFormat.Font.Name = "Tahoma";
cellExport.SheetOptions.NoteFormat.Font.Size = 8.0F;
cellExport.SheetOptions.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.Gray40Percent;
cellExport.SheetOptions.TitlesFormat.Font.Bold = true;
cellExport.SheetOptions.TitlesFormat.Font.Name = "Arial";
//worksheet culture format
workSheet.FormatsExport.CultureName = "en-NZ";
workSheet.FormatsExport.Currency = "#,###,##0.00";
workSheet.FormatsExport.DateTime = "dd/MM/yyyy H:mm";
workSheet.FormatsExport.Float = "#,###,##0.00";
workSheet.FormatsExport.Integer = "#,###,##0";
workSheet.FormatsExport.Time = "H:mm";
//worksheet outlook format;
workSheet.Options.AggregateFormat.Font.Name = "Arial";
workSheet.Options.CustomDataFormat.Font.Name = "Arial";
workSheet.Options.DefaultFont.Name = "Arial";
workSheet.Options.FooterFormat.Font.Name = "Arial";
workSheet.Options.HeaderFormat.Font.Name = "Arial";
workSheet.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet.Options.HyperlinkFormat.Font.Name = "Arial";
workSheet.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single;
workSheet.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left;
workSheet.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top;
workSheet.Options.NoteFormat.Font.Bold = true;
workSheet.Options.NoteFormat.Font.Name = "Tahoma";
workSheet.Options.NoteFormat.Font.Size = 8.0F;
workSheet.Options.TitlesFormat.Font.Bold = true;
workSheet.Options.TitlesFormat.Font.Name = "Arial";
workSheet.Options.CustomDataFormat.Borders.Bottom.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet.Options.CustomDataFormat.Borders.Left.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet.Options.CustomDataFormat.Borders.Right.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet.Options.CustomDataFormat.Borders.Top.Color = Spire.DataExport.XLS.CellColor.Blue;
workSheet.SheetName = "Worksheet";
workSheet.AutoFitColWidth = true;
StripStyle stripStyle1 = new Spire.DataExport.XLS.StripStyle();
Cell mcell = new Spire.DataExport.XLS.Cell();
mcell.Column = 1;
mcell.Row = 2;
if (dsProjectGroup.Tables.Count > 0)
{
if (dsProjectGroup.Tables[0].Rows.Count > 0)
{
mcell.Value = String.Format("{0} - Contractor's Quote", dsProjectGroup.Tables[0].Rows[0]["Name"]);
}
else
{
mcell.Value = String.Format("Contractor's Quote");
}
}
else
{
mcell.Value = String.Format("Contractor's Quote");
}
mcell.CellType = Spire.DataExport.XLS.CellType.String;
workSheet.HeaderRows = 15;
workSheet.Cells.Add(mcell);
intCompanyInfoStartRow = 8;
intCompanyInfoStartColum = 1;
CellPicture pic1 = new Spire.DataExport.XLS.CellPicture();
pic1.FileName = strLogo; //need to find out where the logo is
pic1.Name = "Logo";
cellExport.Pictures.Add(pic1);
CellImage img1 = new Spire.DataExport.XLS.CellImage();
img1.Column = 3;
img1.PictureName = "Logo";
img1.Row = 1;
workSheet.Images.Add(img1);
Cell mcell1 = new Spire.DataExport.XLS.Cell();
mcell1.Column = intCompanyInfoStartColum;
mcell1.Row = intCompanyInfoStartRow;
mcell1.Value = "Claim No:";
mcell1.CellType = CellType.String;
workSheet.Cells.Add(mcell1);
Cell mcell2 = new Spire.DataExport.XLS.Cell();
mcell2.Column = intCompanyInfoStartColum;
mcell2.Row = intCompanyInfoStartRow + 1;
mcell2.Value = "Claimant:";
mcell2.CellType = CellType.String;
workSheet.Cells.Add(mcell2);
Cell mcell3 = new Spire.DataExport.XLS.Cell();
mcell3.Column = intCompanyInfoStartColum;
mcell3.Row = intCompanyInfoStartRow + 2;
mcell3.Value = "Site Location:";
mcell3.CellType = CellType.String;
workSheet.Cells.Add(mcell3);
Cell mcell4 = new Spire.DataExport.XLS.Cell();
mcell4.Column = intCompanyInfoStartColum;
mcell4.Row = intCompanyInfoStartRow + 3;
mcell4.Value = "EQR Supervisor:";
mcell4.CellType = CellType.String;
workSheet.Cells.Add(mcell4);
Cell mcell5 = new Spire.DataExport.XLS.Cell();
mcell5.Column = intCompanyInfoStartColum;
mcell5.Row = intCompanyInfoStartRow + 4;
mcell5.Value = "Contractor E-mail:";
mcell5.CellType = CellType.String;
workSheet.Cells.Add(mcell5);
Cell mcell6 = new Spire.DataExport.XLS.Cell();
mcell6.Column = intCompanyInfoStartColum;
mcell6.Row = intCompanyInfoStartRow + 5;
mcell6.Value = "Date:";
mcell6.CellType = CellType.String;
workSheet.Cells.Add(mcell6);
//'add contact detail head field contet
//'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Cell mcell1_2 = new Spire.DataExport.XLS.Cell();
mcell1_2.Column = intCompanyInfoStartColum + 1;
mcell1_2.Row = intCompanyInfoStartRow;
mcell1_2.Value = strClaimNo;
mcell1_2.CellType = CellType.String;
workSheet.Cells.Add(mcell1_2);
Cell mcell2_2 = new Spire.DataExport.XLS.Cell();
mcell2_2.Column = intCompanyInfoStartColum + 1;
mcell2_2.Row = intCompanyInfoStartRow + 1;
mcell2_2.Value = strClaimant;
mcell2_2.CellType = CellType.String;
workSheet.Cells.Add(mcell2_2);
Cell mcell3_2 = new Spire.DataExport.XLS.Cell();
mcell3_2.Column = intCompanyInfoStartColum + 1;
mcell3_2.Row = intCompanyInfoStartRow + 2;
mcell3_2.Value = strSiteLocation;
mcell3_2.CellType = CellType.String;
workSheet.Cells.Add(mcell3_2);
Cell mcell4_2 = new Spire.DataExport.XLS.Cell();
mcell4_2.Column = intCompanyInfoStartColum + 1;
mcell4_2.Row = intCompanyInfoStartRow + 3;
mcell4_2.Value = strEQRSupervisor;
mcell4_2.CellType = CellType.String;
workSheet.Cells.Add(mcell4_2);
Cell mcell5_2 = new Spire.DataExport.XLS.Cell();
mcell5_2.Column = intCompanyInfoStartColum + 1;
mcell5_2.Row = intCompanyInfoStartRow + 4;
mcell5_2.Value = strContractorEmail;
mcell5_2.CellType = CellType.String;
workSheet.Cells.Add(mcell5_2);
Cell mcell6_2 = new Spire.DataExport.XLS.Cell();
mcell6_2.Column = intCompanyInfoStartColum + 1;
mcell6_2.Row = intCompanyInfoStartRow + 5;
// 'mcell6_2.Value = IIf(strScopeDate.Trim() = String.Empty, Today.ToString("dd/MM/yyyy"), strScopeDate)
mcell6_2.Value = DateTime.Now.ToString("dd/MM/yyyy");
mcell6_2.CellType = CellType.String;
workSheet.Cells.Add(mcell6_2);
//'second part of heading
//''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Cell mcell1_3 = new Spire.DataExport.XLS.Cell();
mcell1_3.Column = intCompanyInfoStartColum + 2;
mcell1_3.Row = intCompanyInfoStartRow;
mcell1_3.Value = "Contractor:";
mcell1_3.CellType = CellType.String;
workSheet.Cells.Add(mcell1_3);
Cell mcell2_3 = new Spire.DataExport.XLS.Cell();
mcell2_3.Column = intCompanyInfoStartColum + 2;
mcell2_3.Row = intCompanyInfoStartRow + 1;
mcell2_3.Value = "Address:";
mcell2_3.CellType = CellType.String;
workSheet.Cells.Add(mcell2_3);
Cell mcell3_3 = new Spire.DataExport.XLS.Cell();
mcell3_3.Column = intCompanyInfoStartColum + 2;
mcell3_3.Row = intCompanyInfoStartRow + 2;
mcell3_3.Value = "Accreditation No:";
mcell3_3.CellType = CellType.String;
workSheet.Cells.Add(mcell3_3);
Cell mcell4_3 = new Spire.DataExport.XLS.Cell();
mcell4_3.Column = intCompanyInfoStartColum + 2;
mcell4_3.Row = intCompanyInfoStartRow + 3;
mcell4_3.Value = "Contractor Phone:";
mcell4_3.CellType = CellType.String;
workSheet.Cells.Add(mcell4_3);
Cell mcell5_3 = new Spire.DataExport.XLS.Cell();
mcell5_3.Column = intCompanyInfoStartColum + 2;
mcell5_3.Row = intCompanyInfoStartRow + 4;
mcell5_3.Value = "GST No:";
mcell5_3.CellType = CellType.String;
workSheet.Cells.Add(mcell5_3);
Cell mcell6_3 = new Spire.DataExport.XLS.Cell();
mcell6_3.Column = intCompanyInfoStartColum + 2;
mcell6_3.Row = intCompanyInfoStartRow + 5;
mcell6_3.Value = "Total Price (incl. GST):";
mcell6_3.CellType = CellType.String;
workSheet.Cells.Add(mcell6_3);
//'second part of heading
// '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Cell mcell1_4 = new Spire.DataExport.XLS.Cell();
mcell1_4.Column = intCompanyInfoStartColum + 3;
mcell1_4.Row = intCompanyInfoStartRow;
mcell1_4.Value = strContractor;
mcell1_4.CellType = CellType.String;
workSheet.Cells.Add(mcell1_4);
Cell mcell2_4 = new Spire.DataExport.XLS.Cell();
mcell2_4.Column = intCompanyInfoStartColum + 3;
mcell2_4.Row = intCompanyInfoStartRow + 1;
mcell2_4.Value = strAddress;
mcell2_4.CellType = CellType.String;
workSheet.Cells.Add(mcell2_4);
Cell mcell3_4 = new Spire.DataExport.XLS.Cell();
mcell3_4.Column = intCompanyInfoStartColum + 3;
mcell3_4.Row = intCompanyInfoStartRow + 2;
mcell3_4.Value = strAccreditationNo;
mcell3_4.CellType = CellType.String;
workSheet.Cells.Add(mcell3_4);
Cell mcell4_4 = new Spire.DataExport.XLS.Cell();
mcell4_4.Column = intCompanyInfoStartColum + 3;
mcell4_4.Row = intCompanyInfoStartRow + 3;
mcell4_4.Value = strContractorPhone;
mcell4_4.CellType = CellType.String;
workSheet.Cells.Add(mcell4_4);
Cell mcell5_4 = new Spire.DataExport.XLS.Cell();
mcell5_4.Column = intCompanyInfoStartColum + 3;
mcell5_4.Row = intCompanyInfoStartRow + 4;
mcell5_4.Value = strGSTNo;
mcell5_4.CellType = CellType.String;
workSheet.Cells.Add(mcell5_4);
Cell mcell6_4 = new Spire.DataExport.XLS.Cell();
mcell6_4.Column = intCompanyInfoStartColum + 3;
mcell6_4.Row = intCompanyInfoStartRow + 5;
mcell6_4.Value = strTotalPrice;
mcell6_4.CellType = CellType.String;
workSheet.Cells.Add(mcell6_4);
dsQuery = objScopeService.GetScopeItemsByScopeIdScopeItemStatus(ScopeId, 2);
dsQuery.Tables[0].DefaultView.Sort = "ScopeGroup";
//DataRowView rowView= new DataRowView();
int intRowIndex;
int intColumIndex;
intRowIndex = 15;
intColumIndex = 0;
String strPrevScopeGroup = "****";
String strScopeGroup = String.Empty;
String strPrevArea = "*****";
String strArea = String.Empty;
String strAreaMeasurement = String.Empty;
Boolean blnAreaMeasurementFilled = false;
Cell dynamiccell_1;
intRowIndex = intRowIndex + 1;
int Range1 = intRowIndex;
foreach (DataRowView rowView in dsQuery.Tables[0].DefaultView)
{
DataRow row = rowView.Row;
strScopeGroup = String.Format("{0}", row["ScopeGroup"]);
if (strScopeGroup != strPrevScopeGroup)
{
intRowIndex = intRowIndex + 1;
dynamiccell_1 = new Spire.DataExport.XLS.Cell();
dynamiccell_1.Column = 1;
dynamiccell_1.Row = intRowIndex;
dynamiccell_1.Value = String.Format("{0}", row["ScopeGroup"]);
dynamiccell_1.CellType = CellType.String;
workSheet.Cells.Add(dynamiccell_1);
dynamiccell_1 = new Spire.DataExport.XLS.Cell();
dynamiccell_1.Column = 2;
dynamiccell_1.Row = intRowIndex;
dynamiccell_1.Value = "Description of Works";
dynamiccell_1.CellType = Spire.DataExport.XLS.CellType.String;
workSheet.Cells.Add(dynamiccell_1);
dynamiccell_1 = new Spire.DataExport.XLS.Cell();
dynamiccell_1.Column = 3;
dynamiccell_1.Row = intRowIndex;
dynamiccell_1.Value = "Dimension";
dynamiccell_1.CellType = Spire.DataExport.XLS.CellType.String;
workSheet.Cells.Add(dynamiccell_1);
dynamiccell_1 = new Spire.DataExport.XLS.Cell();
dynamiccell_1.Column = 4;
dynamiccell_1.Row = intRowIndex;
dynamiccell_1.Value = "$ Rate";
dynamiccell_1.CellType = Spire.DataExport.XLS.CellType.String;
workSheet.Cells.Add(dynamiccell_1);
dynamiccell_1 = new Spire.DataExport.XLS.Cell();
dynamiccell_1.Column = 5;
dynamiccell_1.Row = intRowIndex;
dynamiccell_1.Value = "Contractors Quote";
dynamiccell_1.CellType = Spire.DataExport.XLS.CellType.String;
workSheet.Cells.Add(dynamiccell_1);
intRowIndex = intRowIndex + 1;
}
strPrevScopeGroup = strScopeGroup;
strArea = String.Format("{0}", row["Area"]);
strAreaMeasurement = String.Format("{0}", row["AreaMeasurement"]);
dynamiccell_1 = new Spire.DataExport.XLS.Cell();
dynamiccell_1.Column = 1;
dynamiccell_1.Row = intRowIndex;
if (strArea != strPrevArea)
{
dynamiccell_1.Value = strArea;
blnAreaMeasurementFilled = false;
}
else
if (blnAreaMeasurementFilled)
{
dynamiccell_1.Value = "-";
}
else
{
if (strAreaMeasurement.Trim() == String.Empty)
{
dynamiccell_1.Value = "-";
}
else
{
dynamiccell_1.Value = strAreaMeasurement;
}
blnAreaMeasurementFilled = true;
}
strPrevArea = strArea;
dynamiccell_1.CellType = CellType.String;
workSheet.Cells.Add(dynamiccell_1);
//'Note
dynamiccell_1 = new Spire.DataExport.XLS.Cell();
dynamiccell_1.Column = 2;
dynamiccell_1.Row = intRowIndex;
if (!Convert.IsDBNull(row["Description"]))
{
dynamiccell_1.Value = row["Description"];
}
else
{
dynamiccell_1.Value = "";
}
if (!Convert.IsDBNull(row["Item"]))
{
if (String.Format("{0}", row["Item"]).Trim() != String.Empty)
{
dynamiccell_1.Value = String.Format("{0}: {1}", row["Item"], dynamiccell_1.Value);
}
}
dynamiccell_1.CellType = Spire.DataExport.XLS.CellType.String;
workSheet.Cells.Add(dynamiccell_1);
//'QTY
dynamiccell_1 = new Spire.DataExport.XLS.Cell();
dynamiccell_1.Column = 3;
dynamiccell_1.Row = intRowIndex;
if (Convert.IsDBNull(row["Quantity"]))
{
dynamiccell_1.Value = Convert.ToDouble(row["Quantity"]).ToString();
}
else
{
dynamiccell_1.Value = 0;
}
dynamiccell_1.CellType = Spire.DataExport.XLS.CellType.String;
dynamiccell_1.NumericFormat = "#,###,##0.00";
workSheet.Cells.Add(dynamiccell_1);
//'Rate
dynamiccell_1 = new Spire.DataExport.XLS.Cell();
dynamiccell_1.Column = 4;
dynamiccell_1.Row = intRowIndex;
if (Convert.IsDBNull(row["Rate"]))
{
dynamiccell_1.Value = Convert.ToDouble(row["Rate"]).ToString();
}
else
{
dynamiccell_1.Value = 0;
}
dynamiccell_1.CellType = Spire.DataExport.XLS.CellType.String;
dynamiccell_1.NumericFormat = "#,###,##0.00";
workSheet.Cells.Add(dynamiccell_1);
//'Cost
dynamiccell_1 = new Spire.DataExport.XLS.Cell();
dynamiccell_1.Column = 5;
dynamiccell_1.Row = intRowIndex;
if (Convert.IsDBNull(row["Cost"]))
{
dynamiccell_1.Value = Convert.ToDouble(row["Cost"]).ToString("c");
}
else
{
dynamiccell_1.Value = Convert.ToDouble(0).ToString("c");
}
dynamiccell_1.CellType = Spire.DataExport.XLS.CellType.String;
dynamiccell_1.NumericFormat = "#,###,##0.00";
workSheet.Cells.Add(dynamiccell_1);
intRowIndex = intRowIndex + 1;
}
int Range2 = intRowIndex - 1;
intFootTotalStartRow = intRowIndex;
intFootTotalStartColum = 4;
//'heading
Cell mcell1_5 = new Spire.DataExport.XLS.Cell();
mcell1_5.Column = intFootTotalStartColum;
mcell1_5.Row = intFootTotalStartRow + 2;
mcell1_5.Value = "Subtotal (excl. GST)";
mcell1_5.CellType = Spire.DataExport.XLS.CellType.String;
workSheet.Cells.Add(mcell1_5);
Cell mcell2_5 = new Spire.DataExport.XLS.Cell();
mcell2_5.Column = intFootTotalStartColum;
mcell2_5.Row = intFootTotalStartRow + 3;
mcell2_5.Value = "Add 15% GST";
mcell2_5.CellType = Spire.DataExport.XLS.CellType.String;
workSheet.Cells.Add(mcell2_5);
Cell mcell3_5 = new Spire.DataExport.XLS.Cell();
mcell3_5.Column = intFootTotalStartColum;
mcell3_5.Row = intFootTotalStartRow + 4;
mcell3_5.Value = "Total Incl. GST";
mcell3_5.CellType = Spire.DataExport.XLS.CellType.String;
workSheet.Cells.Add(mcell3_5);
Cell mcell1_7 = new Spire.DataExport.XLS.Cell();
mcell1_7.Column = intFootTotalStartColum + 1;
mcell1_7.Row = intFootTotalStartRow + 2;
mcell1_7.Value = ApprovedexcludeGstCost;
mcell1_7.CellType = Spire.DataExport.XLS.CellType.String;
workSheet.Cells.Add(mcell1_7);
Cell mcell2_7 = new Spire.DataExport.XLS.Cell();
mcell2_7.Column = intFootTotalStartColum + 2;
mcell2_7.Row = intFootTotalStartRow + 3;
mcell2_7.Value = ApprovedGSTCost;
mcell2_7.CellType = Spire.DataExport.XLS.CellType.String;
workSheet.Cells.Add(mcell2_7);
Cell mcell3_7 = new Spire.DataExport.XLS.Cell();
mcell3_7.Column = intFootTotalStartColum + 3;
mcell3_7.Row = intFootTotalStartRow + 4;
mcell3_7.Value = ApprovedInGSTCost;
mcell3_7.CellType = Spire.DataExport.XLS.CellType.String;
workSheet.Cells.Add(mcell3_7);
DataTable InitialDataTable = new DataTable();
//'connect to dataset
workSheet.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
//'workSheet.DataTable = dsQuery.Tables(0)
workSheet.DataTable = InitialDataTable;
//'workSheet.SQLCommand = oleDbCommand
workSheet.StartDataCol = Convert.ToByte(1);
cellExport.Sheets.Add(workSheet);
try
{
cellExport.SaveToHttpResponse(String.Format("{0}.xls", strNewExcelFileName), context.Response);
result = strExcelFile;
}
catch (Exception)
{
}
return(result);
}
public void ProcessRequest(HttpContext context)
{
ManagementService managementservice = new ManagementService();
ScopeServices scopeservices = new ScopeServices();
managementservice.SQLConnection = ConnectDb.SQLConnection;
UserProfile contactProfile = new UserProfile();
Project project = new Project();
User contactUser = new User();
long userId;
string FirstName; string LastName;
string Email;
string Contact1; string Contact2; string Contact3;
string Address;
string Suburb; int SuburbId; string City; int CityId;
string PostCode; string Region; int RegionId;
string Country; int CountryId;
string ProjectName; string ClaimNumber; string EstimatedTime;
string StartDate = ""; string ScopeDate = "";
string AssessmentDate = ""; string QuotationDate = "";
string FinishDate = ""; string ProjectGroupName;
int ProjectGroupId; int Priority; string Hazard; int status;
try { userId = Convert.ToInt32(context.Request.QueryString["userId"]); }
catch { userId = 0; }
try { FirstName = context.Request.QueryString["FirstName"]; }
catch { FirstName = ""; }
try { LastName = context.Request.QueryString["LastName"]; }
catch { LastName = ""; }
try { Email = context.Request.QueryString["Email"]; }
catch { Email = ""; }
try { Contact1 = context.Request.QueryString["Contact1"]; }
catch { Contact1 = ""; }
try { Contact2 = context.Request.QueryString["Contact2"]; }
catch { Contact2 = ""; }
try { Contact3 = context.Request.QueryString["Contact3"]; }
catch { Contact3 = ""; }
try { Address = context.Request.QueryString["Address"]; }
catch { Address = ""; }
try { Suburb = context.Request.QueryString["Suburb"]; }
catch { Suburb = ""; }
try { SuburbId = Convert.ToInt32(context.Request.QueryString["SuburbId"]); }
catch { SuburbId = 0; }
try { City = context.Request.QueryString["City"]; }
catch { City = ""; }
try { CityId = Convert.ToInt32(context.Request.QueryString["CityId"]); }
catch { CityId = 0; }
try { PostCode = context.Request.QueryString["PostCode"]; }
catch { PostCode = ""; }
try { Region = context.Request.QueryString["Region"]; }
catch { Region = ""; }
try { RegionId = Convert.ToInt32(context.Request.QueryString["RegionId"]); }
catch { RegionId = 0; }
try { Country = context.Request.QueryString["Country"]; }
catch { Country = ""; }
try { CountryId = Convert.ToInt32(context.Request.QueryString["CountryId"]); }
catch { CountryId = 0; }
try { ProjectName = context.Request.QueryString["ProjectName"]; }
catch { ProjectName = ""; }
try { ClaimNumber = context.Request.QueryString["ClaimNumber"]; }
catch { ClaimNumber = ""; }
try { EstimatedTime = context.Request.QueryString["EstimatedTime"]; }
catch { EstimatedTime = ""; }
try { StartDate = context.Request.QueryString["StartDate"]; }
catch { }
try { ScopeDate = context.Request.QueryString["ScopeDate"]; }
catch { }
try { AssessmentDate = context.Request.QueryString["AssessmentDate"]; }
catch { }
try { QuotationDate = context.Request.QueryString["QuotationDate"]; }
catch { }
try { FinishDate = context.Request.QueryString["FinishDate"]; }
catch { }
try { ProjectGroupName = context.Request.QueryString["ProjectGroupName"]; }
catch { ProjectGroupName = ""; }
try { ProjectGroupId = Convert.ToInt32(context.Request.QueryString["ProjectGroupId"]); }
catch { ProjectGroupId = 0; }
try { Priority = Convert.ToInt32(context.Request.QueryString["Priority"]); }
catch { Priority = 0; }
try { Hazard = context.Request.QueryString["Hazard"]; }
catch { Hazard = ""; }
try { status = Convert.ToInt32(context.Request.QueryString["status"]); }
catch { status = 0; }
contactUser.Email = Email.Trim();
contactUser.Type = 0;
userId = managementservice.CreateUser(contactUser, userId);
long userProfileId;
contactProfile.UserId = userId;
contactProfile.FirstName = FirstName.Trim();
contactProfile.LastName = LastName.Trim();
contactProfile.Contact1 = Contact1.Trim();
contactProfile.Contact2 = Contact2.Trim();
contactProfile.Contact3 = Contact3.Trim();
contactProfile.Email = Email.Trim();
userProfileId = managementservice.CreateUserProfile(contactProfile);
contactProfile.UserProfileId = userProfileId;
VoucherCodeFunctions cVoucherCode = new VoucherCodeFunctions();
String strIdentifier = String.Format("{0}{1}", userProfileId, cVoucherCode.GenerateVoucherCodeGuid(16));
contactProfile.Identifier = strIdentifier;
managementservice.UpdateUserProfileIdentifier(contactProfile);
string filename = DateTime.UtcNow.ToString("yyyyMMddHHmmss");
string fname = "", virtualpath = "";
System.Text.StringBuilder str = new System.Text.StringBuilder();
try
{
if (context.Request.Files.Count > 0)
{
HttpFileCollection files = context.Request.Files;
string myactualfilename = "";
for (int i = 0; i < files.Count; i++)
{
HttpPostedFile file = files[i];
myactualfilename = file.FileName;
var p = file.FileName.Split('.');
var extention = myactualfilename.Split('.');
string ext = extention[extention.Length - 1];
filename = filename + '.' + ext;
string projectpath = "http://koreprojects.com";
string folderPath = context.Server.MapPath(projectpath + "/Images/" + contactProfile.Identifier);
if (!System.IO.Directory.Exists(folderPath))
{
System.IO.Directory.CreateDirectory(folderPath);
}
fname = context.Server.MapPath(projectpath + "/Images/" + contactProfile.Identifier + "/" + filename);
file.SaveAs(fname);
//context.Response.Write(filename);
}
}
}
catch
{
//context.Response.Write("un");
}
if (filename != String.Empty)
{
contactProfile.PersonalPhoto = filename;
}
managementservice.UpdateUserProfile(contactProfile);
project.ContactId = userId;
project.ProjectOwnerId = managementservice.GetProjectOwnerByContactId(userId).ProjectOwnerId;
project.Address = Address.Trim();
project.Suburb = Suburb;
if (Suburb != string.Empty)
{
project.SuburbID = SuburbId;
}
project.City = City;
if (City != string.Empty)
{
project.CityID = CityId;
}
project.Region = Region;
if (RegionId > 0)
{
project.RegionID = RegionId;
}
project.Country = Country;
if (CountryId > 0)
{
project.CountryID = CountryId;
}
project.Name = ProjectName.Trim();
project.EQCClaimNumber = ClaimNumber.Trim();
project.EstimatedTime = EstimatedTime.Trim();
project.StartDate = Convert.ToDateTime(StartDate);
project.ScopeDate = Convert.ToDateTime(ScopeDate);
project.ProjectStatusId = 0;
if (ProjectGroupId > 0)
{
project.GroupID = ProjectGroupId;
project.GroupName = ProjectGroupName;
}
else
{
project.GroupID = 0;
project.GroupName = String.Empty;
}
project.AssessmentDate = Convert.ToDateTime(AssessmentDate);
project.QuotationDate = Convert.ToDateTime(QuotationDate);
project.FinishDate = Convert.ToDateTime(FinishDate);
project.Priority = Priority;
project.Hazard = Hazard.Trim();
long newProjectId;
newProjectId = managementservice.CreateProject(project);
project.ProjectId = newProjectId;
UserProjectStatusValue userProjectStatusValue = new UserProjectStatusValue();
userProjectStatusValue.ProjectId = project.ProjectId;
userProjectStatusValue.UserId = userId;
userProjectStatusValue.UserProjectStatusValue = status;
managementservice.CreateUserProjectStatusValue(userProjectStatusValue);
int projectCredit = 0;
try
{
DataSet dsUserAccount = new DataSet();
dsUserAccount = managementservice.GetUserAccountByUserID(userId);
if (dsUserAccount.Tables[0].Rows.Count > 0)
{
projectCredit = int.Parse(dsUserAccount.Tables[0].Rows[0]["ProjectCredit"].ToString());
}
if (projectCredit > 0)
{
managementservice.UpdateUserAccount(userId, projectCredit - 1);
managementservice.CreateUserTransaction(userId, String.Format("Create Project", project.Name), 0, 0, -1, projectCredit - 1);
}
}
catch (Exception w)
{ }
context.Response.ContentType = "image/jpg";
context.Response.AddHeader("Content-Disposition", "attachment; filename=\"" + filename + "\"");
}
