using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.WebControls;
public class Foc
{
public String ProductModel { get; set; }
public String supplierName { get; set; }
public long Totalimportedhandset { get; set; }
public long AGES { get; set; }
public Decimal Warranty { get; set; }
public Decimal NonWarranty { get; set; }
public Decimal TotalConsumtion { get; set; }
public Decimal Total_Issued_Spare { get; set; }
public Decimal Spare_To_Import_Ratio { get; set; }
}
public class FocDetails
{
public String Model { get; set; }
public String Items { get; set; }
public Decimal Warranty { get; set; }
public Decimal NonWarranty { get; set; }
public Decimal TotalConsumtion { get; set; }
public Decimal Total_Issued_Spare { get; set; }
public Decimal Spare_To_Import_Ratio { get; set; }
}
public partial class NewReports_FocClaim : System.Web.UI.Page
{
public static DataTable GetDataTableFromSqlQuery(String query)
{
string waltonCrmConnectionString = ConfigurationManager.ConnectionStrings["RBSynergyConnectionString"].ConnectionString;
var dt = new DataTable();
using (var connection = new SqlConnection(waltonCrmConnectionString))
{
connection.Open();
var dataAdapter = new SqlDataAdapter(query, connection);
dataAdapter.SelectCommand.CommandTimeout = 6000;
dataAdapter.Fill(dt);
}
return dt;
}
public static List<T> DataTableToList<T>(DataTable table) where T : class, new()
{
List<T> list = new List<T>();
foreach (var row in table.AsEnumerable())
{
T obj = new T();
foreach (var prop in obj.GetType().GetProperties())
{
try
{
PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
}
catch
{
continue;
}
}
list.Add(obj);
}
return list;
}
protected void Page_Load(object sender, EventArgs e)
{
String query = @"select pm.ProductModel,
(select COUNT(1) Totalimportedhandset
from tblBarCodeInv b
where b.Model=pm.ProductModel) Totalimportedhandset,
ISNULL((select DATEDIFF(day, ReleaseDate, GETDATE()) AGES
from (
select Model,ReleaseDate ,
Row_number() OVER( partition BY Model ORDER BY ReleaseDate) RowNumber
from tblCellPhoneDepriciationPrice p
) X where X.RowNumber=1and X.Model=pm.ProductModel),0) AGES,
ISNULL((select sum(CASE when sm.WarrantyAvailable=1 then 1 else 0 end) Warranty
--,sum(CASE when sm.WarrantyAvailable<>1 then 1 else 0 end) NonWarranty
from WSMS.dbo.Requisition r
left join WSMS.dbo.RequisitionDetail rd
on r.RequisitionID=rd.RequisitionID
inner join WSMS.dbo.ServiceMaster sm
on r.ServiceId=sm.ServiceID
where rd.ItemCode not in ('SPAR.MOBL.00000','SPAR.MOBL.99999')
and rd.ItemStatus='Used' and Sm.Model=pm.ProductModel
group by sm.Model),0) Warranty,
ISNULL((select --sum(CASE when sm.WarrantyAvailable=1 then 1 else 0 end) Warranty,
sum(CASE when sm.WarrantyAvailable<>1 then 1 else 0 end) NonWarranty
from WSMS.dbo.Requisition r
left join WSMS.dbo.RequisitionDetail rd
on r.RequisitionID=rd.RequisitionID
inner join WSMS.dbo.ServiceMaster sm
on r.ServiceId=sm.ServiceID
where rd.ItemCode not in ('SPAR.MOBL.00000','SPAR.MOBL.99999')
and rd.ItemStatus='Used' and Sm.Model=pm.ProductModel
group by sm.Model),0) NonWarranty,
(select supplierName from tblSupplierModelInfo where Model=pm.ProductModel) supplierName
from tblProductMaster pm
where ProductType='Cell Phone'
and pm.DateAdded>='2014-01-01'
order by pm.ProductModel";
List<Foc> focs = DataTableToList<Foc>(GetDataTableFromSqlQuery(query));
if (focs.Any())
{
foreach (var foc in focs)
{
foc.TotalConsumtion = foc.Warranty + foc.NonWarranty;
if (foc.Totalimportedhandset != 0)
{
foc.Total_Issued_Spare =
Math.Round((foc.TotalConsumtion/foc.Totalimportedhandset)*100, 2);
foc.Spare_To_Import_Ratio =
Math.Round((foc.Warranty/foc.Totalimportedhandset)*100, 2);
}
}
grdvFoc.DataSource = focs;
grdvFoc.DataBind();
grdvFoc.UseAccessibleHeader = true;
grdvFoc.HeaderRow.TableSection = TableRowSection.TableHeader;
}
}
[WebMethod]
public static List<FocDetails> GetDetails(String productModel, String totalimportedhandset)
{
String query = String.Format(@"select Sm.Model,'Touch' Items,sum(CASE when sm.WarrantyAvailable=1 then 1 else 0 end) Warranty,sum(CASE when sm.WarrantyAvailable<>1 then 1 else 0 end) NonWarranty
from WSMS.dbo.Requisition r
left join WSMS.dbo.RequisitionDetail rd
on r.RequisitionID=rd.RequisitionID
inner join WSMS.dbo.ServiceMaster sm
on r.ServiceId=sm.ServiceID
where rd.ItemName not like '%LCD%' and rd.ItemName like '%Touch%'
and rd.ItemStatus='Used' and sm.Model='{0}'
group by sm.Model
union all
select Sm.Model,'LCD' Items,sum(CASE when sm.WarrantyAvailable=1 then 1 else 0 end) Warranty,sum(CASE when sm.WarrantyAvailable<>1 then 1 else 0 end) NonWarranty
from WSMS.dbo.Requisition r
left join WSMS.dbo.RequisitionDetail rd
on r.RequisitionID=rd.RequisitionID
inner join WSMS.dbo.ServiceMaster sm
on r.ServiceId=sm.ServiceID
where rd.ItemName like '%LCD%' and rd.ItemName not like '%Touch%'
and rd.ItemStatus='Used' and sm.Model='{0}'
group by sm.Model
union all
select Sm.Model,'TouchWithLcd' Items,sum(CASE when sm.WarrantyAvailable=1 then 1 else 0 end) Warranty,sum(CASE when sm.WarrantyAvailable<>1 then 1 else 0 end) NonWarranty
from WSMS.dbo.Requisition r
left join WSMS.dbo.RequisitionDetail rd
on r.RequisitionID=rd.RequisitionID
inner join WSMS.dbo.ServiceMaster sm
on r.ServiceId=sm.ServiceID
where rd.ItemName like '%LCD%' and rd.ItemName like '%Touch%'
and rd.ItemStatus='Used' and sm.Model='{0}'
group by sm.Model
union all
select Sm.Model,'SubPcba' Items,sum(CASE when sm.WarrantyAvailable=1 then 1 else 0 end) Warranty,sum(CASE when sm.WarrantyAvailable<>1 then 1 else 0 end) NonWarranty
from WSMS.dbo.Requisition r
left join WSMS.dbo.RequisitionDetail rd
on r.RequisitionID=rd.RequisitionID
inner join WSMS.dbo.ServiceMaster sm
on r.ServiceId=sm.ServiceID
where rd.ItemName like '%sub%'
and rd.ItemStatus='Used' and sm.Model='{0}'
group by sm.Model
union all
select Sm.Model,'Battery' Items,sum(CASE when sm.WarrantyAvailable=1 then 1 else 0 end) Warranty,sum(CASE when sm.WarrantyAvailable<>1 then 1 else 0 end) NonWarranty
from WSMS.dbo.Requisition r
left join WSMS.dbo.RequisitionDetail rd
on r.RequisitionID=rd.RequisitionID
inner join WSMS.dbo.ServiceMaster sm
on r.ServiceId=sm.ServiceID
where rd.ItemName like '%Battery%'
and rd.ItemStatus='Used' and sm.Model='{0}'
group by sm.Model", productModel);
List<FocDetails> focDetails = DataTableToList<FocDetails>(GetDataTableFromSqlQuery(query));
Decimal Totalimportedhandset = Convert.ToDecimal(totalimportedhandset);
foreach (var foc in focDetails)
{
foc.TotalConsumtion = foc.Warranty + foc.NonWarranty;
if (Totalimportedhandset != 0)
{
foc.Total_Issued_Spare =
Math.Round((foc.TotalConsumtion / Totalimportedhandset) * 100, 2);
foc.Spare_To_Import_Ratio =
Math.Round((foc.Warranty / Totalimportedhandset) * 100, 2);
}
}
return focDetails;
}
}