public void Report_1(DateTime Report_date, string typ)
        {
            //default last date
            string last_date = "9999/12/31";

            //current exchanfge rate
            var exchangerate = (from d in db.Exchange_Rate where d.end_date == last_date && d.from_currency_id == 100 && d.to_currency_id == 106 select d.exchange_rate1).FirstOrDefault();//db.GetExchangeRate(dtExchangeRateDate).FirstOrDefault();

            //file path W:r:to type(pdf/excel)
            string Orginal_file_path = string.Empty;

            if (typ.Equals("pdf"))
                Orginal_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Report_Templates"), "Report_1_A.xlsx");

            else
                Orginal_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Report_Templates"), "Report_1_B.xlsx");

            //temp file name
            string copy_file_name = DateTime.Now.Ticks + ".xlsx";
            //temp file path
            string copy_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Images"), copy_file_name);

            //open file
            XlsFile report_1 = new XlsFile(Orginal_file_path);

            //sheet always 1
            report_1.ActiveSheet = 1;

            //get import data
            var daily_trans_import = db.Report_1_Import(Report_date);

            //get import data count
            var daily_trans_import_count = db.Report_1_Import(Report_date).Count();

            //skip if no data
            if (daily_trans_import_count <= 0)
                goto skipImport;

            //print
            int i = 6;

            report_1.SetCellValue(3, 4, exchangerate);

            report_1.SetCellValue(2, 2, Report_date.ToString("dd/MM/yyyy"));

            foreach (var item in daily_trans_import)
            {

                report_1.SetCellValue(i, 2, item.BY_CASH_CHEQUE);
                report_1.SetCellValue(i, 3, item.USD);
                report_1.SetCellValue(i, 4, item.SOS);
                report_1.SetCellValue(i, 5, item.TOTAL_USD);
                report_1.SetCellValue(i, 6, item.TOTAL_SOS);
                i++;
            }

            //skiped import
            skipImport:

            //get export dtaa
            var daily_trans_export = db.Report_1_Export(Report_date);

            //get export count
            var daily_trans_export_count = db.Report_1_Export(Report_date).Count();

            //skip export if no data
            if (daily_trans_export_count <= 0)
                goto skipExport;

            //print export
            i = 14;

            foreach (var item in daily_trans_export)
            {

                report_1.SetCellValue(i, 2, item.BY_CASH_CHEQUE);
                report_1.SetCellValue(i, 3, item.USD);
                report_1.SetCellValue(i, 4, item.SOS);
                report_1.SetCellValue(i, 5, item.TOTAL_USD);
                report_1.SetCellValue(i, 6, item.TOTAL_SOS);
                i++;
            }

            //skipped export
            skipExport:

            //if no import and export return alert msg
            if (daily_trans_import_count <= 0 && daily_trans_export_count <= 0)
            {
                TempData["Warning"] = "No Data To Export";
                return;
            }
            else
            {

                //else print

                report_1.PrintToFit = true;
                report_1.PrintPaperSize = TPaperSize.A4;

                TWorkbookProtectionOptions protoptions = new TWorkbookProtectionOptions();
                protoptions.Window = false;
                protoptions.Structure = false;
                TSharedWorkbookProtectionOptions op = new TSharedWorkbookProtectionOptions();
                op.SharingWithTrackChanges = false;

                report_1.Protection.SetSharedWorkbookProtection("XXXX", op);

                report_1.Protection.SetWorkbookProtection("XXXX", protoptions);
                report_1.Protection.SetModifyPassword("XXXX", true, "XXXXXX");
                report_1.Protection.SetWorkbookProtectionOptions(protoptions);

                string dte = Report_date.ToString("dd/MMM/yyyy");
                dte = dte.Replace("/", "_");
                report_1.Save(copy_file_path);

                if (typ.Equals("pdf"))
                    GeneratePDF(report_1, "Report_1_" + dte);

                else
                {
                    Response.AppendHeader("Content-Disposition", "attachment; filename=Report_1_" + dte + ".xlsx");
                    Response.TransmitFile(copy_file_path);
                    Response.End();
                }

            }
        }
        public void Report_2(IEnumerable<GetReport_Levy_Transactions_Import_Rep2_Result> daily_trans_import, IEnumerable<GetReport_Levy_Transactions_Export_Rep2_Result> daily_trans_Export, string filterType, string exchange_rate, string typ, string copy_file_path, string Orginal_file_path)
        {
            int nocolor = 0;
            int coloronly = 0;

            int leftborder = 0;
            int rightborder = 0;

            int nocolorleft = 0;
            int nocolorright = 0;

            int nocolorbold = 0;
            int colorbold = 0;

            int c = 1;

            //open File
            XlsFile report_2 = new XlsFile(Orginal_file_path);

            //Keeping Impot Sheet Active
            report_2.ActiveSheet = 1;

            //tables for Import & export
            DataTable dtImport = new DataTable();
            // DataTable dtExport = new DataTable();

            //if import data is null , then import will be skipped --using goto skipImport else get the value in dtimport
            try
            {
                if(daily_trans_import!=null)
                dtImport = LinqQueryToDataTable(daily_trans_import.ToList());

                if (dtImport.Rows.Count <= 1)
                    goto skip_Import;

            }
            catch
            { goto skip_Import; }

            //Transpose result for excel
            DataTable dtTranspose1 = GenerateTransposedTable(dtImport);

            //// integer c to handle the cell number in both sheets
            //if (!typ.Equals("pdf"))
            //    c = 1;

            //to set exchange_rate
            report_2.SetCellValue(3, 3 + c, GetDecimal(exchange_rate));

            //to set header
            report_2.SetCellValue(2, 2 + c, filterType);

             nocolor = NoColory(report_2, 7, 2);
             coloronly = ColorOnly(report_2, 7, 2);

             leftborder = ColorAndLeftBorder(report_2, 7, 2);
             rightborder = ColorAndRightBorder(report_2, 7, 2);

             nocolorleft = NoColorAndLeftBorder(report_2, 7, 2);
             nocolorright = NoColorAndRightBorder(report_2, 7, 2);

             nocolorbold = NoColorBold(report_2, 7, 2);
             colorbold = ColorBold(report_2, 7, 2);

            //to print all data
            int i = 7;

            int color1 = 0;
            int color2 = 0;
            int color3 = 0;
            int color4 = 0;

            int datarow = 7;
            foreach (DataRow dr in dtTranspose1.Rows)
            {

                if (dr[1].ToString().Length > 0)
                {
                    if (i % 2 != 0)
                    {
                        color1 = nocolorleft;
                        color2 = nocolor;
                        color3 = nocolorright;
                        color4 = nocolorbold;

                    }
                    else
                    {
                        color1 = leftborder;
                        color2 = coloronly;
                        color3 = rightborder;
                        color4 = colorbold;

                    }

                    report_2.SetCellValue(i, 1 + c, dr[1].ToString().TrimEnd(']').TrimStart('['), color1);
                    report_2.SetCellValue(i, 2 + c, GetDecimal(dr[2].ToString()), color2);
                    report_2.SetCellValue(i, 3 + c, GetDecimal(dr[3].ToString()), color2);
                    report_2.SetCellValue(i, 4 + c, GetDecimal(dr[4].ToString()), color4);
                    report_2.SetCellValue(i, 5 + c, GetDecimal(dr[5].ToString()), color3);
                    datarow++;
                }
                i++;

            }

            int laststyle = TopBorderOnly(report_2, i, 1 + c);
            //last line border
            report_2.SetCellValue(datarow, 1 + c, "", laststyle);
            report_2.SetCellValue(datarow, 2 + c, "", laststyle);
            report_2.SetCellValue(datarow, 3 + c, "", laststyle);
            report_2.SetCellValue(datarow, 4 + c, "", laststyle);
            report_2.SetCellValue(datarow, 5 + c, "", laststyle);

            skip_Import:

            //Keeping Impot Sheet Active
            report_2.ActiveSheet = 2;

            //tables for Import & export
            DataTable dtExport = new DataTable();

            // integer c to handle the cell number in both sheets
            //if (!typ.Equals("pdf"))
            //    c = 1;

            //to set exchange_rate
            report_2.SetCellValue(3, 3 + c, GetDecimal(exchange_rate));

            //to set header
            report_2.SetCellValue(2, 2 + c, filterType);

            //checking export data, if null then skipping printing export  details
            try
            {
                if (daily_trans_Export != null)
                dtExport = LinqQueryToDataTable(daily_trans_Export.ToList());

                if (dtExport.Rows.Count <= 1)
                    goto skipExport;

            }
            catch
            { goto skipExport; }

            //transposing for excel
            DataTable dtTranspose2 = GenerateTransposedTable(dtExport);

            //printing rows
             i = 7;

             color1 = 0;
             color2 = 0;
             color3 = 0;
             color4 = 0;

             datarow = 7;
            foreach (DataRow dr in dtTranspose2.Rows)
            {

                if (dr[1].ToString().Length > 0)
                {
                    if (i % 2 != 0)
                    {
                        color1 = nocolorleft;
                        color2 = nocolor;
                        color3 = nocolorright;
                        color4 = nocolorbold;

                    }
                    else
                    {
                        color1 = leftborder;
                        color2 = coloronly;
                        color3 = rightborder;
                        color4 = colorbold;

                    }

                    report_2.SetCellValue(i, 1 + c, dr[1].ToString().TrimEnd(']').TrimStart('['), color1);
                    report_2.SetCellValue(i, 2 + c, GetDecimal(dr[2].ToString()), color2);
                    report_2.SetCellValue(i, 3 + c, GetDecimal(dr[3].ToString()), color2);
                    report_2.SetCellValue(i, 4 + c, GetDecimal(dr[4].ToString()), color4);
                    report_2.SetCellValue(i, 5 + c, GetDecimal(dr[5].ToString()), color3);
                    datarow++;
                }
                i++;

            }

            laststyle = TopBorderOnly(report_2, i, 1 + c);
            //last line border
            report_2.SetCellValue(datarow, 1 + c, "", laststyle);
            report_2.SetCellValue(datarow, 2 + c, "", laststyle);
            report_2.SetCellValue(datarow, 3 + c, "", laststyle);
            report_2.SetCellValue(datarow, 4 + c, "", laststyle);
            report_2.SetCellValue(datarow, 5 + c, "", laststyle);

            //doing the rest if data is null
            skipExport:

            report_2.PrintToFit = true;
            report_2.PrintPaperSize = TPaperSize.A4;

            TWorkbookProtectionOptions protoptions = new TWorkbookProtectionOptions();
            protoptions.Window = false;
            protoptions.Structure = false;

            TSharedWorkbookProtectionOptions op = new TSharedWorkbookProtectionOptions();
            op.SharingWithTrackChanges = false;

            report_2.Protection.SetSharedWorkbookProtection("XXXX", op);

            report_2.Protection.SetWorkbookProtection("XXXX", protoptions);
            report_2.Protection.SetModifyPassword("XXXX", true, "XXXXXX");
            report_2.Protection.SetWorkbookProtectionOptions(protoptions);

            if (dtImport.Rows.Count <= 1)
                TempData["Warning"] = "No Data To Export";
            else
            {
                report_2.Save(copy_file_path);

                if (typ.Equals("pdf"))
                    GeneratePDF(report_2, "Report_2_"+filterType);

                else
                {
                    Response.AppendHeader("Content-Disposition", "attachment; filename=Report_2_"+filterType+".xlsx");
                    Response.TransmitFile(copy_file_path);
                    Response.End();
                }
            }
        }
        public void Report_3(IEnumerable<GetReport3_Levy_Products_Result> daily_trans, string filterType, string exchange_rate, string typ)
        {
            //var bK = daily_trans;
            //if (bK.Count() <= 0)
            //{
            //    TempData["Warning"] = "No Data To Export";
            //    return;
            //}

            string Orginal_file_path = string.Empty;

            if (typ.Equals("pdf"))
                Orginal_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Report_Templates"), "Report_3_B.xlsx");

            else
                Orginal_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Report_Templates"), "Report_3_A.xlsx");

            //temp file name
            string copy_file_name = DateTime.Now.Ticks + ".xlsx";

            //temp file path
            string copy_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Images"), copy_file_name);
            //copy to temp destination

            XlsFile report_3 = new XlsFile(Orginal_file_path);

            int c = 0;
            if (!typ.Equals("pdf"))
                c = 1;

            report_3.ActiveSheet = 1;

            report_3.SetCellValue(3, 3 + c, GetDecimal(exchange_rate));
            report_3.SetCellValue(2, 1 + c, filterType);

            //get color 1
            int col1 = report_3.AddFormat(GetColor1(report_3.GetCellVisibleFormatDef(6, 2)));

            //get colo1 2
            int col2 = report_3.AddFormat(GetColor2(report_3.GetCellVisibleFormatDef(6, 2)));

            int style = col1;

            int i = 6;
            string category_1 = string.Empty;

            int dataExist = 0;

            int j = 0;
            foreach (var item in daily_trans)
            {
                dataExist = 1;
                if (i != 6 && category_1.Equals(item.Category_Name))
                    report_3.MergeCells(i - 1, 1, i, 1);

                else
                {
                    if (style == col1)
                        style = col2;
                    else
                        style = col1;
                }

                //style = res_arr[j];

                category_1 = item.Category_Name;

                report_3.SetCellValue(i, 1 + c, item.Category_Name, style);

                if (item.Goods_Name.Equals("All"))
                {
                    //get current style and add bold
                    TFlxFormat tformat = GetBold(report_3.GetCellVisibleFormatDef(i, 1 + c));
                    int res2 = report_3.AddFormat(tformat);
                    //end  style modification

                    report_3.SetCellValue(i, 2 + c, item.Goods_Name, res2);
                    report_3.SetCellValue(i, 3 + c, item.Unit, res2);
                    report_3.SetCellValue(i, 4 + c, item.Total_Quantity, res2);
                    report_3.SetCellValue(i, 5 + c, item.Total_Amount_in_SOS, res2);
                    report_3.SetCellValue(i, 6 + c, item.Total_Amount_in_USD, res2);

                }
                else
                {
                    report_3.SetCellValue(i, 2 + c, item.Goods_Name, style);
                    report_3.SetCellValue(i, 3 + c, item.Unit, style);
                    report_3.SetCellValue(i, 4 + c, item.Total_Quantity, style);
                    report_3.SetCellValue(i, 5 + c, item.Total_Amount_in_SOS, style);
                    report_3.SetCellValue(i, 6 + c, item.Total_Amount_in_USD, style);

                }

                i++;

            }

            if (dataExist <= 0)
            {
                TempData["Warning"] = "No Data To Export";
                return;
            }
            else
            {

                report_3.PrintToFit = true;
                report_3.PrintPaperSize = TPaperSize.A4;

                TWorkbookProtectionOptions protoptions = new TWorkbookProtectionOptions();
                protoptions.Window = false;
                protoptions.Structure = false;

                TSharedWorkbookProtectionOptions op = new TSharedWorkbookProtectionOptions();
                op.SharingWithTrackChanges = false;

                report_3.Protection.SetSharedWorkbookProtection("XXXX", op);
                report_3.Protection.SetWorkbookProtection("XXXX", protoptions);
                report_3.Protection.SetModifyPassword("XXXX", true, "XXXXXX");
                report_3.Protection.SetWorkbookProtectionOptions(protoptions);

                report_3.Save(copy_file_path);

                if (typ.Equals("pdf"))
                    GeneratePDF(report_3, "Report_3_"+filterType);
                else
                {
                    Response.AppendHeader("Content-Disposition", "attachment; filename=Report_3_"+filterType+".xlsx");
                    Response.TransmitFile(copy_file_path);
                    Response.End();
                }

            }
        }