Find Alternate Monday,1st Monday,third Monday and like this

GO
/****** Object:  StoredProcedure [common].[DateInsert]    Script Date: 9/16/2015 5:00:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- PASS @noOfWeek = 0 AND @IsAlternateDate = 1 IF ALTERNATE WEEK NEEDED. 
-- PASS @noOfWeek = 1/2/3/4/5 AND @IsAlternateDate = 0  FOR nth, for example if finding the 3rd Monday, set @noOfWeek=3

-- EXEC [common].[DateInsert] 1,'','','01/01/2015','12/31/2015',2,0,1
ALTER PROCEDURE [common].[DateInsert] 
@ClinicID int,
@StartTime datetime,
@EndTime datetime,
@Start datetime, 
@End datetime,  
@dayno int ,-- 1=Mon, 2=Tue,... 7=Sun
@noOfWeek int, -- nth, for example if finding the 3rd Monday, set @noOfWeek=3
@IsAlternateDate bit 

AS
BEGIN
SET NOCOUNT OFF
SET FMTONLY OFF
DECLARE @COST DECIMAL(18,2)
  
IF OBJECT_ID('dbo.#t') is not null 
 DROP TABLE dbo.#t;

Declare @StartDateOfMonth date

SELECT @StartDateOfMonth= DATEADD(month, DATEDIFF(month, 0, @Start), 0)

CREATE TABLE #t ([Date] datetime,
  [Year] smallint,
  [Quarter] tinyint,
  [Month] tinyint
, [Day] smallint -- from 1 to 366 = 1st to 366th day in a year
, [Week] tinyint -- from 1 to 54 = the 1st to 54th week in a year; 
, [Monthly_week] tinyint -- 1/2/3/4/5=1st/2nd/3rd/4th/5th week in a month
, [Week_day] tinyint -- 1=Mon, 2=Tue, 3=Wed, 4=Thu, 5=Fri, 6=Sat, 7=Sun
);

-- populate the table #t, and the day of week is defined as
-- 1=Mon, 2=Tue, 3=Wed, 4=Thu,5=Fri, 6=Sat, 7=Sun

;WITH   C0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
  C1   AS (SELECT 1 AS c FROM C0 AS A CROSS JOIN C0 AS B),
  C2   AS (SELECT 1 AS c FROM C1 AS A CROSS JOIN C1 AS B),
  C3   AS (SELECT 1 AS c FROM C2 AS A CROSS JOIN C2 AS B),
  C4   AS (SELECT 1 AS c FROM C3 AS A CROSS JOIN C3 AS B), 
  C5   AS (SELECT 1 AS c FROM C4 AS A CROSS JOIN C3 AS B),
  C6   AS (select rn=row_number() over (order by c)  from C5),
  C7   as (select [date]=dateadd(day, rn-1, @Start) FROM C6 WHERE rn <= datediff(day, @Start, @End)+1)

INSERT INTO #t ([year], [quarter], [month], [week], [day], [monthly_week], [week_day], [date])

SELECT datepart(yy, [DATE]), datepart(qq, [date]), datepart(mm, [date]), datepart(wk, [date])
, datediff(day, dateadd(year, datediff(year, 0, [date]), 0), [date])+1
, datepart(week, [date]) -datepart(week, dateadd(month, datediff(month, 0, [date]) , 0))+1
, CASE WHEN datepart(dw, [date])+@@datefirst-1 > 7 THEN (datepart(dw, [date])+@@datefirst-1)%7
ELSE datepart(dw, [date])+@@datefirst-1 END
, [date]
FROM C7
--where [date] between @Start and @End;

--select * from  #t 

-- find nth week/weekend day of each prev/curr/next month
-- eg. find the 2nd Monday of each prev/curr/next month

; with c as (select 
ROW_NUMBER()OVER (ORDER BY [date])AS ROW,
RNK=RANK() over (partition by [month] order by [date] ASC),
Month,Day,Week,
Monthly_week, [Date] -- attention to 'ASC'
from #t
where Week_Day = @dayno -- 1=Mon, 2=Tue,... 7=Sun
--and Monthly_week>=@noOfWeek
)

select * INTO #t2 from c

--select * from #t2 

select ClinicID = @ClinicID,EffectiveDate = @Start,ForDate = [Date],StartTime=@StartTime,EndTime=@EndTime, IsActive=1 -- ,Monthly_week --,ROW--,rn 
from #t2 

where RNK =CASE WHEN ISNULL(@noOfWeek,0) = 0 THEN RNK ELSE @noOfWeek END-- nth, for example if finding the 3rd Monday, set rn=3
and (ROW%2)<> CASE WHEN @IsAlternateDate=1 THEN 0 ELSE 3 END


END

Read Csv File and Upload To database

protected void Upload(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            FileInfo fileInfo = new FileInfo(FileUpload1.PostedFile.FileName);
            if (fileInfo.Name.Contains(".csv"))
            {
                string fileName = fileInfo.Name.Replace(".csv", "").ToString();
                string csvFilePath = Server.MapPath("UploadedCSVFiles") + "\\" + fileInfo.Name;
                //Save the CSV file in the Server inside 'UploadedCSVFiles'   
                FileUpload1.SaveAs(csvFilePath);
                //Fetch the location of CSV file   
                string filePath = Server.MapPath("UploadedCSVFiles") + "\\";
                string strSql = "SELECT * FROM [" + fileInfo.Name + "]";
                string strCsvConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties='text;HDR=YES;'";
                // load the data from CSV to DataTable   
                DataTable dtCsv = new DataTable();
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(strSql, strCsvConnString))
                {
                    adapter.FillSchema(dtCsv, SchemaType.Mapped);
                    adapter.Fill(dtCsv);
                }

                //master foramt csv
                string filePathMaster = Server.MapPath("UploadedCSVFiles") + "\\";
                string csvFilePathMaster = Server.MapPath("UploadedCSVFiles") + "\\Master-getdysonca-orderimport-format.csv";
                string strSqlMaster = "SELECT * FROM [Master-getdysonca-orderimport-format.csv]";
                string strCsvConnStringMaster = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePathMaster + ";" + "Extended Properties='text;HDR=YES;'";
                // load the data from CSV to DataTable   
                DataTable dtCsvMaster = new DataTable();
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(strSqlMaster, strCsvConnStringMaster))
                {
                    adapter.FillSchema(dtCsvMaster, SchemaType.Mapped);
                    adapter.Fill(dtCsvMaster);
                }

                string uploadedFileHeaderString = ConvertHeaderToString(csvFilePath);
                string formatOfUploadedFileHeaderString = ConvertHeaderToString(csvFilePathMaster);
//check template 
                if (uploadedFileHeaderString != formatOfUploadedFileHeaderString)
                {
                    lblmsg.Text = "Template of csv file is different.";
                }
                else
                {
                    foreach (DataRow row in dtCsv.Rows)
                    {
                        object eMail = row["EMAIL"];
                        //check those field which cannot be blank 
                        if (eMail == DBNull.Value || row["PRODUCT01"] == DBNull.Value || row["BILL_ADDRESS1"] == DBNull.Value || row["BILL_CITY"] == DBNull.Value || row["BILL_STATE"] == DBNull.Value || row["BILL_ZIPCODE"] == DBNull.Value
                            || row["SHIP_TO_ADDRESS1"] == DBNull.Value || row["SHIP_TO_CITY"] == DBNull.Value || row["SHIP_TO_STATE"] == DBNull.Value || row["ORDER_NUMBER"] == DBNull.Value || row["ORDER_DATE"] == DBNull.Value)
                        {
                            lblmsg.Text = "Email, Product, Billing address, Shipping Address, Order Number, Order Date field(s) cannot be blank.";

                        }
                        else
                        {
                            var dt = dtCsv;
                            var rowNo=dtCsv.Rows.Count;
                            if (rowNo > 0)
                            {
                                string errMsg = "";
                                int purchaseID = 0;
                                List<int> list = new List<int>();
                                string consString = ConfigurationManager.ConnectionStrings["ecommerceConnectionString"].ToString();
                                using (SqlConnection con = new SqlConnection(consString))
                                {
                                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                                    {
                                        //Set the database table name
                                        sqlBulkCopy.DestinationTableName = "dbo.ImportedRawData";
                                        con.Open();
                                        sqlBulkCopy.WriteToServer(dt);
                                        con.Close();
                                    }

                                    int campaignID = Convert.ToInt32(ConfigurationManager.AppSettings["CampaignID"]);
                                    using (var cmd = con.CreateCommand())
                                    {
                                        con.Open();
                                        cmd.CommandText = "[dbo].[ImportDataFromCSV]";
                                        cmd.CommandType = CommandType.StoredProcedure;
                                        cmd.Parameters.Add(new SqlParameter("CampaignID", campaignID));
                                        cmd.Parameters.Add(new SqlParameter("FileName", fileInfo.Name));
                                        
                                        using (var reader = cmd.ExecuteReader())
                                        {
                                            if (reader.HasRows)
                                            {

                                                    //var result = reader[0];

                                                    while (reader.Read())
                                                    {
                                                        if (Convert.ToString(reader["ErrMsg"]) == "BLANK")
                                                        {
                                                        //purchaseID = Convert.ToInt32(reader["ImportedPurchaseID"]);
                                                        // errMsg = Convert.ToString(reader["ErrMsg"]);
                                                        list.Add(Convert.ToInt32(reader["ID"]));
                                                        }

                                                        else
                                                            list.Add(Convert.ToInt32(reader["ErrMsg"]));
                                                    }
                                                //purchaseID = Convert.ToInt32(list[0]);
                                                //errMsg = list[1].ToString();
                                            }
                                        }
                                        con.Close();
                                    }
                                    for (int j = 0; j < list.Count() ; j++)
                                    {
                                        purchaseID = Convert.ToInt32(list[j]);
                                        ImportedPurchase importedPurchase = InsertDataIntoImportedPurchase(purchaseID);

                                        //send mail
                                        new PaymentModule().MailRichConfirmationForImport(importedPurchase);
                                    }
                                }

                                if (errMsg == "")
                                {
                                    lblmsg.Text = string.Format("({0}) records has been loaded to the table.", rowNo, fileName);
                                }
                                else
                                    lblmsg.Text = errMsg;
                            }
                            else
                            {
                                lblmsg.Text = "File is empty.";
                            }
                        }

                    }

                }
            }
            else
            {
                lblmsg.Text = "Upload csv file only.";
            }
        }
    }

    public string ConvertHeaderToString(string fileNameWithPath)
    {
        //bool firstLineOfChunk = true;
        string columnData = null;
        bool firstLineOfFile = true;
        using (var sr = new StreamReader(fileNameWithPath))
        {
            string line = null;

            while ((line = sr.ReadLine()) != null)
            {
                if (firstLineOfFile)
                {
                    columnData = line;
                    firstLineOfFile = false;
                    continue;
                }
            }
        }
        return columnData;
    }


    public bool CompareDataTable(DataTable t1, DataTable t2)
    {
        if (t1.Rows.Count != t2.Rows.Count)
            return false;

        foreach (DataColumn dc in t1.Columns)
        {
            for (int i = 0; i < t1.Rows.Count; i++)
            {
                if (t1.Rows[i][dc.ColumnName] != t2.Rows[i][dc.ColumnName])
                {
                    return false;
                }
            }
        }
        return true;
    }

Post form data along with kendo grid data

    ** Suppose I have three kendo grids and two textboxs in my page . Now I want to post all data along with data of three grid . I did this by below style.



  @model DAL.ViewModel.ProfileViewModel
        @{
            ViewBag.Title = "Profile";
            Layout = "~/Views/Shared/_LayoutAfterLogin.cshtml";
        }

        <h2>Profile</h2>

        <div>
            <h4>ApplicationUser</h4>
            <hr />
            <dl class="dl-horizontal"></dl>

            <form id="frmProfile">
                <div>
                    <label>Email<span class="mandatory"></span></label>

                    @Html.Kendo().TextBoxFor(model => model.Email)
                </div>
                <div>
                    <label>UserName<span class="mandatory"></span></label>

                    @Html.Kendo().TextBoxFor(model => model.UserName)
                </div>
            </form>

            @(Html.Kendo().Grid<DAL.ViewModel.PhoneViewModel>()
            .Name("PhoneGrid")
            .Columns(columns =>
            {
                columns.Bound(p => p.PhoneID).Groupable(false);
                columns.Bound(p => p.PhoneType).Width(160);
                columns.Bound(p => p.PhoneNumber).Width(120);
                columns.Bound(p => p.IsPrimary).Width(120);

                columns.Command(command => command.Destroy()).Width(90);
            })
            .ToolBar(toolBar =>
                {
                    toolBar.Create();
                    // toolBar.Save();
                })
            .Editable(editable => editable.Mode(GridEditMode.InCell))
            .Pageable()
            .Sortable()
            .Scrollable()
            .HtmlAttributes(new { style = "height:430px;" })
            .DataSource(dataSource => dataSource
                .Ajax()
                .Batch(true)
                .ServerOperation(false)
                .Events(events => events.Error("error_handler"))
                .Model(model =>
                {
                    model.Id(p => p.PhoneID);
                    model.Field(p => p.PhoneID).Editable(false);
                })
                .PageSize(20)
                .Read(read => read.Action("PhoneList", "Account"))
                                .Create(create => create.Action("AddPhone", "Account"))
                                .Update(update => update.Action("EditPhone", "Account"))
                                .Destroy(destroy => destroy.Action("DeletePhone", "Account"))
            )
            )


        </div>
        <p>
            <button type="button" id="btnSave">Save</button>
            @Html.ActionLink("Edit", "Edit", new { /* id = Model.PrimaryKey */ }) |
            @Html.ActionLink("Back to List", "Index")
        </p>



        //jquery
        $("#btnSave").on("click", function () {
                sendData();
            });

            function sendData() {

                var grid = $("#PhoneGrid").data("kendoGrid"),
                    parameterMap = grid.dataSource.transport.parameterMap;

                //get the new and the updated records
                var currentData = grid.dataSource.data();
                var updatedRecords = [];
                var newRecords = [];

                for (var i = 0; i < currentData.length; i++) {
                    if (currentData[i].isNew()) {
                        //this record is new
                        newRecords.push(currentData[i].toJSON());
                    } else if (currentData[i].dirty) {
                        updatedRecords.push(currentData[i].toJSON());
                    }
                }

                //this records are deleted
                var deletedRecords = [];
                for (var i = 0; i < grid.dataSource._destroyed.length; i++) {
                    deletedRecords.push(grid.dataSource._destroyed[i].toJSON());
                }

                var serializedData = $("#frmProfile").serializeObject();

                var data = {};
                $.extend(data, parameterMap({ updated: updatedRecords }), parameterMap({ deleted: deletedRecords }), parameterMap({ new: newRecords }));

                var finaldata = {};
                $.extend(finaldata, parameterMap({ phone: data }), parameterMap({ email: data }), parameterMap({ address: data }), parameterMap({ pagedata: serializedData }));
                $.ajax({
                    url: '@Url.Action("UpdateCreateDelete1", "Account")',
                    data: JSON.stringify(finaldata),
                    type: "POST",
                    contentType: 'application/json',
                    dataType: 'json',
                    error: function (e) {
                        alert('error');
                        //Handle the server errors using the approach from the previous example
                    },
                    success: function () {
                        grid.dataSource._destroyed = [];
                        //refresh the grid - optional
                        // grid.dataSource.read();
                    }
                })
            }

            jQuery.fn.serializeObject = function () {
                var arrayData, objectData;
                arrayData = this.serializeArray();
                objectData = {};

                $.each(arrayData, function () {
                    var value;

                    if (this.value != null) {
                        value = this.value;
                    } else {
                        value = '';
                    }

                    if (objectData[this.name] != null) {
                        if (!objectData[this.name].push) {
                            objectData[this.name] = [objectData[this.name]];
                        }

                        objectData[this.name].push(value);
                    } else {
                        objectData[this.name] = value;
                    }
                });

                return objectData;
            };



       //action method
         public ActionResult UpdateCreateDelete1(
                   [Bind(Prefix = "phone.updated")]List<PhoneViewModel> updatedPhone,
                   [Bind(Prefix = "phone.new")]List<PhoneViewModel> newPhone,
                   [Bind(Prefix = "phone.deleted")]List<PhoneViewModel> deletedPhone,

                   [Bind(Prefix = "email")]List<PhoneViewModel> emaillist,
                   [Bind(Prefix = "address")]List<PhoneViewModel> addresslist,

                   [Bind(Prefix = "pagedata")] ProfileViewModel pagedata

                    )
                {
        }

Convert time to HH:MM AM format by jquery

var dt = new Date();   

function settime(dt) {
    var hours = dt.getHours()
    var minutes = dt.getMinutes()
    if (minutes < 10)
    minutes = "0" + minutes
    var suffix = "AM";
    if (hours >= 12) {
    suffix = "PM";
    hours = hours - 12;
    }
    if (hours == 0) {
    hours = 12;
    }
    // set the date back to the current date
    return hours + ":" + minutes + " " + suffix
    }

//call the function
        var time=settime(dt);

//set the time in your text box
        $('#CallTime').val(time);

jQuery UI Touch Punch

Using Touch Punch is as easy as 1, 2…

Just follow these simple steps to enable touch events in your jQuery UI app:

1. Include jQuery and jQuery UI on your page.

   <script src="http://code.jquery.com/jquery-1.7.2.min.js"></script>
   <script src="http://code.jquery.com/ui/1.8.21/jquery-ui.min.js"></script>

2. Include Touch Punch after jQuery UI and before its first use.
   Please note that if you are using jQuery UI's components, Touch Punch must be included after jquery.ui.mouse.js, as Touch Punch modifies its behavior.

   <script src="jquery.ui.touch-punch.min.js"></script>

3. There is no 3. Just use jQuery UI as expected and watch it work at the touch of a finger.

   <script>$('#widget').draggable();</script>

Reference: http://touchpunch.furf.com/

How to target iOS with jQuery

Mobile Safari for iOS comes with it’s own little quirks, like scaling your background images or needing a lighter page footprint when on 3G. Fortunately targeting the devices that pack mobile Safari is pretty easy.  Here is a little snippet:

//INCLUDE LATEST VERSION OF JQUERY
<script src="http://code.jquery.com/jquery-latest.js"></script>
$(document).ready(function(){
var device = navigator.userAgent.toLowerCase();
var ios = device.match(/(iphone|ipod|ipad)/);
if (ios) {
     //ADD CLASSES HERE THAT WILL ONLY APPLY TO IOS
     $("some-element").addClass("some-class");
     }
}); 

Reference:

http://www.rufusmedia.com/journal/2011/05/how-to-target-ios-with-jquery 

remove scroll from fullcalendar in week view and dayview

 function resizeCalendar() {
        var currentView = $('#calendar').fullCalendar('getView');
        if (currentView.name === 'agendaWeek' || currentView.name === 'agendaDay') {
            currentView.setHeight(9999);
        }
    }
    $(window).on('resize', resizeCalendar);


 $('#calendar').fullCalendar({
                    selectable: true,
                    viewDisplay: resizeCalendar //this is the trick
})

JQUERY:Calculate age years ,month,days

function getAge(dateString) {
    var now = new Date();
    var today = new Date(now.getYear(), now.getMonth(), now.getDate());

    var yearNow = now.getYear();
    var monthNow = now.getMonth();
    var dateNow = now.getDate();

    var dob = new Date(dateString.substring(6, 10),
                     dateString.substring(0, 2) - 1,
                     dateString.substring(3, 5)
                     );

    var yearDob = dob.getYear();
    var monthDob = dob.getMonth();
    var dateDob = dob.getDate();
    var age = {};
    var ageString = "";
    var yearString = "";
    var monthString = "";
    var dayString = "";


    yearAge = yearNow - yearDob;

    if (monthNow >= monthDob)
        var monthAge = monthNow - monthDob;
    else {
        yearAge--;
        var monthAge = 12 + monthNow - monthDob;
    }

    if (dateNow >= dateDob)
        var dateAge = dateNow - dateDob;
    else {
        monthAge--;
        var dateAge = 31 + dateNow - dateDob;

        if (monthAge < 0) {
            monthAge = 11;
            yearAge--;
        }
    }

    age = {
        years: yearAge,
        months: monthAge,
        days: dateAge
    };

    if (age.years > 1) yearString = " years";
    else yearString = " year";
    if (age.months > 1) monthString = " months";
    else monthString = " month";
    if (age.days > 1) dayString = " days";
    else dayString = " day";


    if ((age.years > 0) && (age.months > 0) && (age.days > 0))
        ageString = age.years + yearString + ", " + age.months + monthString + ", and " + age.days + dayString ;
    else if ((age.years == 0) && (age.months == 0) && (age.days > 0))
        ageString = "Only " + age.days + dayString ;
    else if ((age.years > 0) && (age.months == 0) && (age.days == 0))
        ageString = age.years + yearString;
    else if ((age.years > 0) && (age.months > 0) && (age.days == 0))
        ageString = age.years + yearString + " and " + age.months + monthString ;
    else if ((age.years == 0) && (age.months > 0) && (age.days > 0))
        ageString = age.months + monthString + " and " + age.days + dayString;
    else if ((age.years > 0) && (age.months == 0) && (age.days > 0))
        ageString = age.years + yearString + " and " + age.days + dayString;
    else if ((age.years == 0) && (age.months > 0) && (age.days == 0))
        ageString = age.months + monthString;
    else ageString = "Could not calculate age!";

    return ageString;
}